【推薦】Sql Server 不常見應用之一

獲取表的基本信息、字段列表、存儲過程參數列表

——通過知識共享樹立個人品牌。

一、獲取表的基本信息

SELECT [TableName] = [Tables].name ,

        [TableOwner] = [Schemas].name ,

        [TableCreateDate] = [Tables].create_date ,

        [TableModifyDate] = [Tables].modify_date

FROM    sys.tables AS [Tables]

        INNER JOIN sys.schemas AS [Schemas] ON [Tables].schema_id = [Schemas].schema_id

WHERE   [Tables].name = '案卷目錄'

效果如圖一所示：

圖一 指定表的基本信息

二、根據表名獲取字段列表

SELECT [ColumnName] = [Columns].name ,

        [SystemTypeName] = [Types].name ,

        [Precision] = [Columns].precision ,

        [Scale] = [Columns].scale ,

        [MaxLength] = [Columns].max_length ,

        [IsNullable] = [Columns].is_nullable ,

        [IsRowGUIDCol] = [Columns].is_rowguidcol ,

        [IsIdentity] = [Columns].is_identity ,

        [IsComputed] = [Columns].is_computed ,

        [IsXmlDocument] = [Columns].is_xml_document ,

        [Description] = [Properties].value

FROM    sys.tables AS [Tables]

        INNER JOIN sys.columns AS [Columns] ON [Tables].object_id = [Columns].object_id

        INNER JOIN sys.types AS [Types] ON [Columns].system_type_id = [Types].system_type_id

                                           AND is_user_defined = 0

                                           AND [Types].name <> 'sysname'

        LEFT OUTER JOIN sys.extended_properties AS [Properties] ON [Properties].major_id = [Tables].object_id

                                                              AND [Properties].minor_id = [Columns].column_id

                                                              AND [Properties].name = 'MS_Description'

WHERE   [Tables].name =案卷目錄

ORDER BY [Columns].column_id

效果如圖二所示：

圖二 指定表的字段列表信息

三、獲取指定存儲過程參數列表

SELECT sc.name AS 參數名 ,

        st.name AS 類型 ,

        sc.length AS 長度

FROM    syscolumns sc

        INNER JOIN sysobjects so ON so.id = sc.id

        INNER JOIN systypes st ON sc.xtype = st.xtype

WHERE   so.name = 'SP_Pagination'

效果如圖三所示： 

圖三 指定存儲過程的參數列表