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

Source: Internet
Author: User

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]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.