SQL script queries database tables, data, structures, constraints, and other operations

This article describes how to use SQL scripts to query database tables, data, structures, constraints, and other operations. For more information, see

This article describes how to use SQL scripts to query database tables, data, structures, constraints, and other operations. For more information, see

1. query all tables in the current database

The code is as follows:
SELECT
O. object_id AS TableId,
TableName = O. name,
TableDesc = O. type
FROM sys. columns C
Inner join sys. objects O
On c. [object_id] = O. [object_id]
And o. type = 'u'
And o. is_ms_shipped = 0
Inner join sys. types T
On c. user_type_id = T. user_type_id
Left join sys. extended_properties PTB
On ptb. class = 1
And ptb. minor_id = 0
And c. [object_id] = PTB. major_id
Where c. column_id = 1
Order by TableName

2. query all fields, data, and constraints of the current table.

The code is as follows:
Select
TabName = O. NAME,
ColumnLine = C. column_id,
ColumnName = C. name,
TypeNum = T. name,
TypeLength = C. max_length,
FState = ISNULL (G. value, N ''),
IsAbleNull = case when c. is_nullable = 1 THEN n' √ 'else N'' END,
DefaultData = ISNULL (D. definition, N ''),
IsIdentity = case when c. is_identity = 1 THEN n' √ 'else n' END,
IsPrimary = case when exists (SELECT 1 FROM sysobjects where xtype = 'PK' and parent_obj = c. [object_id] and name in (
SELECT name FROM sysindexes WHERE indid in (SELECT indid FROM sysindexkeys WHERE id = c. [object_id] AND colid = c. column_id) then '√ 'else' 'end,
IsForeign = case when exists (select * from sysforeignkeys fk where C. [object_id] = FK. fkeyid and c. column_id = FK. fkey) then '√ 'else' 'end,
TabForeignName = ISNULL (IDX. FKName, N ''),
OutNameCol = ISNULL (IDX. ns, N '')
FROM sys. columns C
Inner join sys. objects O
On c. [object_id] = O. [object_id]
And o. type = 'u'
And o. is_ms_shipped = 0
Inner join sys. types T
On c. user_type_id = T. user_type_id
Left JOIN sys. extended_properties G
On c. [object_id] = G. major_id and c. column_id = g. minor_id
Left join sys. default_constraints D
On c. [object_id] = D. parent_object_id
And c. column_id = D. parent_column_id
And c. default_object_id = D. [object_id]
Left join sysforeignkeys fk
On C. [object_id] = FK. fkeyid
And C. column_id = FK. fkey
Left join -- Index and primary key information
(
SELECT
IDX. fkeyid,
IDX. fkey,
FKName = o. name,
Ns = ss. name
FROM sysforeignkeys IDX
Inner join sys. objects O
On idx. rkeyid = O. [object_id]
And o. type = 'u'
And o. is_ms_shipped = 0
Left join syscolumns ss
On IDX. rkeyid = ss. id
And IDX. RKEY = SS. COLID
) IDX
On c. [object_id] = IDX. fkeyid
And c. column_id = IDX. fkey

Where o. name = n' {0} '------ name of the table to be queried
Order by o. name, C. column_id

3. Fields

Add single quotes varchar, char, nvarchar, nchar, text, ntext, datetime
Int, numeric, and bit are not required.

Length: [binary], [char], [decimal], [nchar], [numeric], [nvarchar], [varbinary] [varchar]
Do not include: [bigint], [bit], [datetime], [float], [image], [int], [xml], [timestamp], [tinyint],
[Uniqueidentifier], [money], [ntext], [real], [smalldatetime], [smallint], [smallmoney],
[SQL _variant], [text]