把 table schema dump 出來的 SQL

gistfile1.sql

DECLARE @tableName nvarchar(30)
SET @tableName ='你的table名字'
 
SELECT
    IC.TABLE_NAME AS [IC_TableName],
    td.value AS [Table Description],
    c.name AS [Column Name],
    IC.DATA_TYPE,
    IC.CHARACTER_MAXIMUM_LENGTH,
    SM.TEXT AS "Column Default Value",
    cd.value AS [Column Description],
    "Allow Nulls" =
        CASE c.isnullable
            WHEN 0 THEN 'False'
            ELSE 'True'
        END
FROM sysobjects t WITH (NOLOCK)
INNER JOIN syscolumns c WITH (NOLOCK)
    ON c.id = t.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS IC WITH (NOLOCK)
    ON c.name = IC.COLUMN_NAME
LEFT OUTER JOIN sys.extended_properties td WITH (NOLOCK)
    ON td.major_id = t.id
    AND td.minor_id = 0
    AND td.name = 'MS_Description'
LEFT OUTER JOIN sys.extended_properties cd WITH (NOLOCK)
    ON cd.major_id = c.id
    AND cd.minor_id = c.colid
    AND cd.name = 'MS_Description'
    AND cd.class_desc = 'OBJECT_OR_COLUMN'
LEFT JOIN dbo.syscomments SM
    ON c.cdefault = SM.id
WHERE t.type = 'u'
AND t.name = @tableName
AND IC.TABLE_NAME = @tableName
ORDER BY t.name, c.colorder;