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

1. query all tables in the current database

Copy codeThe 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.

Copy codeThe 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]