Some tips for SQL SERVER2000

Query table name in 1.SQL Server 2000

Often encountered some forget the table name, at this time only remember the approximate, at this time can be queried system table sysobjects find the table name you want, if you want to find the name of the table containing the user, can be implemented through the following SQL statement,

Select *

From sysobjects

Where name like '%user% '

2. If you know the name of the column and want to find the name of the table that contains the column, you can add the system table syscolumns to find all the table names containing the user in the column name, which you can do by using the following SQL statement

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 table for all field names:

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

The msysobjects is a system object, and the default is hidden. The tools, options, views, displays, and system objects can be displayed.

1. Get the basic field properties of a table

--Get table structure in SQL Server

SELECT syscolumns.name,systypes.name,syscolumns.isnullable,

Syscolumns.length

From syscolumns, systypes

WHERE Syscolumns.xusertype = Systypes.xusertype

and syscolumns.id = object_id (' Your table name ')

2. Get descriptive information for the field

--Get the table structure primary key in SQL Server, and describe

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. Increment field of a separate query table

--Query table increment field individually

Select [Name] from syscolumns where

id=object_id (N ' Your table name ') and ColumnProperty (Id,name, ' isidentity ') =1

4. Get the primary foreign key of the table

--Get Table primary FOREIGN KEY constraint

EXEC sp_helpconstraint ' your table name ';

5. A fairly complete table structure query

--a very comprehensive table structure

EXEC sp_helpconstraint ' your table name ';

SELECT Table name = Case A.colorder when 1 THEN c.name ELSE "End,

Preface = 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,

Number of bytes = A.length,

Length = ColumnProperty (a.id,a.name, ' Precision '),

decimal = Case ISNULL (columnproperty (a.id,a.name, ' Scale '), 0) when 0 THEN ' ELSE CAST (ColumnProperty (a.id,a.name, ' Scale ') as V Archar) End,

Allow NULL = case a.isnullable time 1 THEN ' √ ' ELSE ' end,

Default = ISNULL (D.[text], ""),

Description = ISNULL (E.[value], "")

From Syscolumns A

Left JOIN systypes 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 the library names

--Get all the library names in the server

SELECT * FROM Mastersysdatabases

7. Get all the tables for all libraries on the server

--Get all table names for all libraries 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. Order BY last name stroke:

Select * FROM TableName ORDER by CustomerName Collate Chinese_prc_stroke_ci_as

2. Paging SQL statement

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

3. Get all the 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. Query all databases created by users

SELECT * from Mastersysdatabases D where Sid isn't in (select Sid from Mastersyslogins where name= ' sa ')

Or

Select dbid, name as db_name from mastersysdatabases 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. Use of transactions

SQL Server transactions can be used to prevent misoperation problems after data manipulation using a few temporary SQL statement operations on database tables

Start a transaction

Begin Tran

Insert into TableName Values (...)

If the SQL statement operation is not normal, the transaction is rolled back.

Rolling back a transaction

Rollback Tran

If 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 '

11. Calculate the execution of 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 (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with 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 that are in TABLE1 but not in TABLE2, and all duplicate rows are eliminated. 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 the rows in TABLE1 and TABLE2 and eliminates all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.