Shulou(Shulou.com)06/01 Report--

1.sp_helptext is the text that displays rules, default values, unencrypted stored procedures, user-defined functions, triggers, or views.

2.SQL queries the table in which a field data resides

Select a.name as table name from sysobjects as a left join syscolumns as b on a.id=b.id where b.nameplate 'field name'

1 > query the table based on known fields:

Select a.name from sysobjects a join syscolumns b on a.id=b.id where b.nameplate 'field name'

2 > query the records that match the value of this field:

Select * from table name where field name = field value (table name is the name queried in step 1)

3. Gets all the user tables in the current database

Select Name from sysobjects where xtype='u' and status > = 0

4. List all the table names in the database

Select name from sysobjects where type='U'

5. Get all the fields of a table

Select name from syscolumns where id=object_id ('table name')

Select name from syscolumns where id in (select id from sysobjects where type ='u 'and name =' table name')

The two methods have the same effect.

6. Query the fields and data types of a table

Select column_name,data_type from information_schema.columns

Where table_name = 'table name'

7. Retrieve the fields in the table:

Declare @ list varchar 1000

@ sql nvarchar (1000)

Select @ list=@list+','+b.name from sysobjects a journal syscolumns b where a.id=b.id and a.nameplate 'table A'

Set @ sql='select'+ right (@ list,len (@ list)-1) + 'from Table A'

Exec (@ sql)

8. View views, stored procedures, functions related to a table

Select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like'% table name%'

9. View all views in the current database

Select name as View from sysobjects where xtype='V'

10. View all stored procedures in the current database

Select name as stored procedure name from sysobjects where xtype='P'

11. Query all databases created by the user

Select * from master..sysdatabases D where sid not in (select sid from master..syslogins where name='sa')

Or

Select dbid, name AS DB_NAME from master..sysdatabases where sid 0x01

The use of 12.1 SQL sentences is more frequently used in the combination of 1Q sentences.

"where 1" means to select all "where 1" and none of them.

Such as:

If @ strWhere! ='

Begin

Set @ strSQL = 'select count () as Total from [' + @ tblName +'] where'+ @ strWhere

End

Else

Begin

Set @ strSQL = 'select count () as Total from [' + @ tblName +']'

End

We can write it directly.

Mistake! No catalog entries were found.

Set @ strSQL = 'select count (*) as Total from [' + @ tblName +'] where 1 stable 1'+ @ strWhere

13. Derive a result table by including all rows in TableA but not in TableB and TableC and eliminating all duplicate rows

(select a from tableA) except (select a from tableB) except (select a from tableC)

14. Delete duplicate record

1), delete from tablename where id not in (select max (id) from tablename group by col1,col2,...)

2), select distinct into temp from tablename

Delete from tablename

Insert into tablename select from temp

Evaluation: this operation involves the movement of a large amount of data, which is not suitable for large capacity but data operation.

3), for example, if you import data in an external table, for some reasons, only part of the data is imported for the first time, but it is difficult to determine the specific location, so it is only the next time all of them are imported, which results in a lot of duplicate fields. How to delete duplicate fields

Alter table tablename

-add a self-incrementing column

Add column_b int identity (1 dint 1)

Delete from tablename where column_b not in (

Select max (column_b) from tablename group by column1,column2,...)

Alter table tablename drop column column_b

15.SQL between two tables: update the fields of one table according to the fields of the other table

Easy to write and efficient to update:

Update table1

Set field1=table2.field1

Field2=table2.field2

From table2

Where table1.id=table2.id

In the conventional way, the method of writing is equivalent to a Left join, with the external where as the update number, and all records without where.

Update table1

Set field1= (select top 1 field1 from table2 where table2.id=table1.id)

Where table1.id in (condition)

16. Compare the equality of table B of AMagna:

If (select checksum_agg (binary_checksum (*)) from A)

(select checksum_agg (binary_checksum (*)) from B)

Print 'equal'

Else

Print 'unequal'

17. Qualified or unqualified name of the table, indexed view, or queue for which space usage information is requested

Exec sp_spaceused 'tablename'

18. View the hard drive partition:

EXEC master..xp_fixeddrives

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.