SQL SERVER2000 skills

1. query table name in SQL Server 2000

I often encounter situations where I forget the table name. I only remember the approximate one. At this time, I can query the system table Sysobjects to find the expected table name. For example, I want to find the table name that contains the user, you can use the following SQL statement to implement,

Select *

From sysobjects

Where name like '% user %'

2. if you know the column name and want to find the table name that contains the column, you can add the system table syscolumns. To find all the table names whose column name contains the user, 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 %'

)

3. SQL SERVER

View All table names:

Select name from sysobjects where type = 'U'

Query the names of all fields in the 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

4. ACCESS

View All table names:

Select name from MSysObjects where type = 1 and flags = 0

MSysObjects is a system object, which is hidden by default. You can use tools, options, views, displays, and system objects to display them.

1. Get basic field attributes of the table

-- Obtain the table structure in SqlServer

SELECT syscolumns. name, policypes. name, syscolumns. isnullable,

Syscolumns. length

FROM syscolumns, policypes

WHERE syscolumns. xusertype = policypes. xusertype

AND syscolumns. id = object_id ('your table name ')

2. Obtain the field description.

-- Obtain the primary key and description of the table structure in SqlServer.

Declare @ table_name as varchar (max)

Set @ table_name = 'your table name'

Select sys. columns. name, sys. types. name, sys. columns. max_length, sys. columns. is_nullable,

(Select count (*) from sys. identity_columns where sys. identity_columns.object_id = sys. columns. object_id and sys. columns. column_id = sys. identity_columns.column_id) as is_identity,

(Select value from sys. extended_properties where sys. extended_properties.major_id = sys. columns. object_id and sys. extended_properties.minor_id = sys. columns. column_id) as description

From sys. columns, sys. tables, sys. types where sys. columns. object_id = sys. tables. object_id and sys. columns. system_type_id = sys. types. system_type_id and sys. tables. name = @ table_name order by sys. columns. column_id

3. Independently query incremental fields of the table

-- Separately query the incremental field of the table

Select [name] from syscolumns where

Id = object_id (n' your table name') and COLUMNPROPERTY (id, name, 'isidentity ') = 1

4. Obtain the table's primary and Foreign keys

-- Obtain the table's primary and foreign key constraints

Exec sp_helpconstraint 'your table name ';

5. Complete table structure query

-- Very comprehensive table structure

Exec sp_helpconstraint 'your table name ';

SELECT Table name = CASE a. colorder WHEN 1 THEN c. name ELSE ''end,

Order = a. colorder,

Field name = a. name,

Id = case columnproperty (a. id, a. name, 'isidentity ') WHEN 1 then' √ 'else' END,

Primary Key = CASE

When exists (SELECT * FROM sysobjects WHERE xtype = 'pk'

AND name IN (SELECT [name] FROM sysindexes WHERE id = a. id

AND indid IN (SELECT indid FROM sysindexkeys WHERE id = a. id

AND colid IN (SELECT colid FROM syscolumns WHERE id = a. id

AND name = a. name) then' √ 'else' END,

Type = B. name,

Bytes = a. length,

Length = COLUMNPROPERTY (a. id, a. name, 'precision '),

Decimal = case isnull (COLUMNPROPERTY (. id,. name, 'Scale'), 0) WHEN 0 THEN ''else cast (COLUMNPROPERTY (. id,. name, 'Scale') as varchar) END,

Allow null = CASE a. isnullable WHEN 1 THEN '√ 'else'' END,

Default Value = ISNULL (d. [text], ''),

Description = ISNULL (e. [value], '')

FROM syscolumns

Left join policypes B ON a. xtype = B. xusertype

Inner join sysobjects c ON a. id = c. id AND c. xtype = 'U' AND c. name <> 'dtproperties'

Left join syscomments d ON a. cdefault = d. id

Left join sys. extended_properties e ON a. id = e. class AND a. colid = e. minor_id

Order by c. name, a. colorder

6. Get all database names

-- Obtain the names of all databases on the server

Select * from mastersysdatabases

7. Retrieve all tables of all databases on the server

-- Obtain the names of all tables in all databases on the server.

Use master

Declare @ db_name varchar (100)

Declare @ SQL varchar (200)

Declare cur_tables cursor

For

Select name from sysdatabases/* where name like 'by _ % '*/

Open cur_tables

Fetch next from cur_tables into @ db_name

While @ fetch_status = 0

Begin

-- Set @ db_name = @ db_name + '. dbo. sysobjects'

Print @ db_name

Set @ SQL = 'select * from' + @ db_name + '. dbo. sysobjects where xtype = ''u '''

Exec (@ SQL)

Fetch next from cur_tables into @ db_name

End

Close cur_tables

Deallocate cur_tables

Go

1. sort by strokes of the Last Name:

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

2. Paging SQL statements

Select * from (select (row_number () OVER (order by tab. ID Desc) as rownum, tab. * from table name As tab) As t where rownum between start position And end position

3. Obtain all user tables in the current database

Select * from sysobjects where xtype = 'U' and category = 0

4. Obtain all fields in a table

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

5. View views, stored procedures, and functions related to 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. query all databases created by the user

Select * from mastersysdatabases D where sid not in (select sid from mastersyslogins where name = 'sa ')

Or

Select dbid, name AS DB_NAME from mastersysdatabases where sid <> 0x01

8. query the fields and Data Types of a table

Select column_name, data_type from information_schema.columns

Where table_name = 'table name'

9. Use transactions

When using temporary SQL statement operations on database tables, you can use SQL SERVER transaction processing to prevent misoperation problems after data operations.

Start transaction

Begin tran

Insert Into TableName Values (...)

If the SQL statement operation is abnormal, the transaction is rolled back.

Roll back a transaction

Rollback tran

If the SQL statement is normal, the transaction is committed and the data is committed to the database.

Commit transactions

Commit tran

10. query by full-text match

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 the SQL statement query time

Declare @ d datetime

Set @ d = getdate ()

Select * from SYS_ColumnProperties select [statement execution time (MS)] = datediff (MS, @ d, getdate ())

12. Description: several advanced query Operators

A: UNION operator

The UNION operator combines two other result tables (such as TABLE1 and TABLE2) and removes any duplicate rows from the table to generate a result table. When ALL is used together with UNION (that is, union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either TABLE1 or TABLE2.

B: Random t operator

The distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When ALL is used with distinct T (distinct t all), duplicate rows are not eliminated.

C: INTERSECT Operator

The INTERSECT operator derives a result table by only including the rows in TABLE1 and TABLE2 and eliminating all repeated rows. When ALL is used with INTERSECT (intersect all), duplicate rows are not eliminated.