Querying table names, column names, and field types in SQL Server 2000

Often encountered some forget the table name situation, at this time only remember a approximate, at this time can be queried by the system table sysobjects to find the table name, such as to find the name of the table containing the user, can be implemented by the following SQL statement,

Select *
From sysobjects
Where name like '%user% '

If you know the column name and want to find the name of the table that contains the column, you can add the system table syscolumns, and if you want to find all the table names that contain user in the column name, you can use the following SQL statement to implement
Select *
From sysobjects s
Where Exists (
Select *
From syscolumns
Where ID = s.id and name like '%user% '
)

View all table names:
Select name from sysobjects where type= ' U '

All field names for the query table:
Select name from syscolumns Where id=object_id (' Table name ')

SELECT * FROM Information_schema.tables
SELECT * FROM Information_schema.views
SELECT * FROM Information_schema.columns

View all table names:
Select name from Msysobjects where type=1 and flags=0

Msysobjects is a system object, which is hidden by default. It can be displayed by tools, options, views, displays, system objects, and so on.

1. Sort by last name stroke:
Select * from TableName Order by CustomerName Collate chinese_prc_stroke_ci_as

2. Paging SQL statements
SELECT * FROM (SELECT (Row_number () to Tab.id Desc) as rownum,tab.* from table name as tab) as T where rownum between Start position and end position

3. Get all user tables in the current database
SELECT * from sysobjects where xtype= ' U ' and category=0

4. Get all the fields of a table
Select name from syscolumns where id=object_id (' Table name ')

5. View the views, stored procedures, functions associated with a table
Select a.* from sysobjects A, syscomments b where a.id = b.ID and b.text like '% table name% '

6. View all stored procedures in the current database
Select name as stored procedure name from sysobjects where xtype= ' P '

7. Querying 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

8. Querying the fields and data types of a table
Select Column_name,data_type from Information_schema.columns
WHERE table_name = ' table name '

9. Using Transactions
SQL Server transactions can be used when working with temporary SQL statements on database tables to prevent errors from being detected after data manipulation.
Start a transaction
Begin Tran
Insert into TableName Values (...)
If the SQL statement operation is not working correctly, the transaction is rolled back.
Rolling back a transaction
Rollback Tran
When the SQL statement is working correctly, the transaction is committed and the data is submitted to the database.
Commit a transaction
Commit Tran
10. Search by full-text matching method
Field name like N '%[^a-za-z0-9]china[^a-za-z0-9]% '
OR field name like N '%[^a-za-z0-9]china '
OR field name like N ' china[^a-za-z0-9]% '
OR field name like N ' China

11. Calculate execution SQL statement query time
DECLARE @d datetime
Set @d=getdate ()
SELECT * FROM sys_columnproperties SELECT [Statement execution takes time (milliseconds)]=datediff (Ms,@d,getdate ())

12. Description: Several advanced query operation words
A:union operator
The UNION operator derives a result table by combining the other two result tables (for example, TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with the Union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator derives a result table by including all rows in TABLE1 but not in TABLE2 and eliminating all duplicate rows. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.
C:intersect operator
The INTERSECT operator derives a result table by including only rows in TABLE1 and TABLE2 and eliminating all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.

Querying table names, column names, and field types in SQL Server 2000