Sql Server 不常见应用之一：获取表的基本信息、字段列表、存储过程参数列表...-优快云博客

本文介绍如何利用 SQL Server 获取表的基本信息、字段列表及存储过程参数，包括表名、所有者、创建与修改日期、字段名称、数据类型、精度、最大长度、是否允许为空、是否为唯一标识符等详细信息。

【推荐】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'

效果如图三所示：