SQLServer: 提取表元数据(描述、字段及其数据类型)

我试图找到一种方法来提取有关 SQLServer (2008)中的表的信息。
我需要的数据包括 表的描述(由属性窗口中的 Description 属性填充)、该表的 字段列表以及它们各自的 数据类型。

有什么办法可以提取这些元数据吗？我想我必须使用一些 sys sp，但我不确定是哪一个。

237897 次浏览
小开

有关表和列的一般信息可以在这些表中找到:

select * from INFORMATION_SCHEMA.TABLES
select * from INFORMATION_SCHEMA.COLUMNS

表描述是一个扩展属性，您可以从 sys.tended _ properties 查询它们:

select
TableName = tbl.table_schema + '.' + tbl.table_name,
TableDescription = prop.value,
ColumnName = col.column_name,
ColumnDataType = col.data_type
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col
ON col.table_name = tbl.table_name
AND col.table_schema = tbl.table_schema
LEFT JOIN sys.extended_properties prop
ON prop.major_id = object_id(tbl.table_schema + '.' + tbl.table_name)
AND prop.minor_id = 0
AND prop.name = 'MS_Description'
WHERE tbl.table_type = 'base table'
小开
最佳答案

为了获得描述数据，很遗憾，您必须使用 sysoobject/syscolumn 来获得 id:

SELECT      u.name + '.' + t.name AS [table],
td.value AS [table_desc],
c.name AS [column],
cd.value AS [column_desc]
FROM        sysobjects t
INNER JOIN  sysusers u
ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
ON      td.major_id = t.id
AND     td.minor_id = 0
AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
ON      cd.major_id = c.id
AND     cd.minor_id = c.colid
AND     cd.name = 'MS_Description'
WHERE t.type = 'u'
ORDER BY    t.name, c.colorder

您可以使用 info-schema 进行调用，但是您必须连接以及调用 OBJECT _ ID ()-那么这样做有什么意义呢？

小开

你可以试试 sp_help <Name of object>

小开

使用对象目录视图:

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],   CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE’;

使用信息架构视图

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS
小开

如果您只是想以一种方便的方式查看信息，那么 Red Gate 的 SQL 提示符可能会有所帮助。

如果将鼠标悬停在查询窗口中的对象文本上，SQLPrompt 将在工具提示中显示 MS _ Description 扩展属性文本。单击工具提示将打开一个对话框，显示列信息和对象的 DDL。

Http://www.red-gate.com/products/sql-development/sql-prompt/

小开

如果可以使用的话。NET 代码我建议使用 SMO: http://msdn.microsoft.com/en-us/library/ms162169.aspx，在你的特殊情况下，它应该是 Table 类 < a href = “ http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management. mo.Table.aspx”rel = “ nofollow”> http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.SMO.Table.aspx 与使用特定于版本的系统视图和表相比，这将是一种更具可移植性的解决方案。

如果你打算定期使用这个控制台应用，你可能需要编写一个简单的代码，比如使用运行时 t4代码生成器 http://msdn.microsoft.com/en-us/library/ee844259.aspx

如果这只是一个一次性的任务，那么您可以使用 LiveDoco (http://www.livedoco.com)导出到 XML 的特性和一个可选的 XSLT 转换，或者我确信有免费的工具可以做到这一点。 这个看起来不错: http://sqldbdoc.codeplex.com/-通过 XSLT 支持 XML，但是我不确定您是否可以在选定的表中运行它(使用 LiveDoco 就可以)。

小开

如果您使用 Java 代码提取查询，可以使用一个很好的类 ResultSetMetaData，它可以检索列名和列的属性(类型和长度)。

例子

ResultSet rs = null;


rs = sql.executeQuery();


if (rs != null) {
if (rs.next()) {
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 1; i <= rsmd.getColumnCount(); i++) {
System.out.println("column name: "
+ rsmd.getColumnName(i));
System.out.println("column size: "
+ rsmd.getColumnDisplaySize(i));
}
}
小开

我使用这个 SQL 代码来获取关于一个列的所有信息。

SELECT
COL.COLUMN_NAME,
ORDINAL_POSITION,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX,
NUMERIC_SCALE,
DATETIME_PRECISION,
IS_NULLABLE,
CONSTRAINT_TYPE,
COLUMNPROPERTY(object_id(COL.TABLE_NAME), COL.COLUMN_NAME, 'IsIdentity') IS_IDENTITY,
COLUMNPROPERTY(object_id(COL.TABLE_NAME), COL.COLUMN_NAME, 'IsComputed') IS_COMPUTED


FROM INFORMATION_SCHEMA.COLUMNS COL
LEFT OUTER JOIN
(
SELECT COLUMN_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE A
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
WHERE A.TABLE_NAME = 'User'
) CONS
ON COL.COLUMN_NAME = CONS.COLUMN_NAME
WHERE COL.TABLE_NAME = 'User'
小开 
  SELECT
sc.name AS ColumnName
,ep.*
FROM
sys.columns AS sc
INNER JOIN sys.extended_properties AS ep
ON ep.major_id = sc.[object_id]
AND ep.minor_id = sc.column_id
WHERE


--here put your desired table
sc.[object_id] = OBJECT_ID('[Northwind].[dbo].[Products]')


-- this is optional, remove this and you get all extended props
AND ep.name = 'MS_Description'
小开

取决于你想要多少元数据，这对我来说是有效的: 哪里会有像 Northwind.dbo 这样的东西。产品还是只有产品

SELECT c.name Field,
t.name Type,
c.Precision,
c.Scale,
c.is_nullable,
c.collation_name
FROM sys.columns c
INNER JOIN sys.types t ON t.system_type_id=c.system_type_id
WHERE object_id=object_id('<table to inspect>')
ORDER BY column_id
小开 
select Col.name Columnname,prop.Value Description, tbl.name Tablename, sch.name schemaname
from sys.columns col  left outer join  sys.extended_properties prop
on prop.major_id =  col.object_id and prop.minor_id = col.column_id
inner join sys.tables tbl on col.object_id =  tbl.object_id
Left outer join sys.schemas sch on sch.schema_id = tbl.schema_id
小开

我最喜欢@Andomar 的回答，但我也需要专栏的描述。下面是他修改后的查询，其中也包括这些查询。(取消 WHERE 子句的最后一部分的注释，只返回其中任一描述为非空的行)。

SELECT
TableName = tbl.table_schema + '.' + tbl.table_name,
TableDescription = tableProp.value,
ColumnName = col.column_name,
ColumnDataType = col.data_type,
ColumnDescription = colDesc.ColumnDescription
FROM information_schema.tables tbl
INNER JOIN information_schema.columns col
ON col.table_name = tbl.table_name
LEFT JOIN sys.extended_properties tableProp
ON tableProp.major_id = object_id(tbl.table_schema + '.' + tbl.table_name)
AND tableProp.minor_id = 0
AND tableProp.name = 'MS_Description'
LEFT JOIN (
SELECT sc.object_id, sc.column_id, sc.name, colProp.[value] AS ColumnDescription
FROM sys.columns sc
INNER JOIN sys.extended_properties colProp
ON colProp.major_id = sc.object_id
AND colProp.minor_id = sc.column_id
AND colProp.name = 'MS_Description'
) colDesc
ON colDesc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name)
AND colDesc.name = col.COLUMN_NAME
WHERE tbl.table_type = 'base table'
--AND tableProp.[value] IS NOT NULL OR colDesc.ColumnDescription IS NOT null
小开

我刚做完一个。Net 库，其中包含一些有用的查询，这些查询返回代码 gen/t4模板的强类型 C # 对象。

Nuget SqlMeta

工程项目工地

Github 源代码

/// <summary>
///     Get All Table Names
/// </summary>
/// <returns></returns>
public List<string> GetTableNames()
{
var sql = @"SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U'
AND name <> 'sysdiagrams'
order by name asc";


return databaseWrapper.Call(connection => connection.Query<string>(
sql: sql))
.ToList();
}


/// <summary>
///     Get table info by schema and table or null for all
/// </summary>
/// <param name="schema"></param>
/// <param name="table"></param>
/// <returns></returns>
public List<SqlTableInfo> GetTableInfo(string schema = null, string table = null)
{
var result = new List<SqlTableInfo>();


var sql = @"SELECT
c.TABLE_CATALOG AS [TableCatalog]
,   c.TABLE_SCHEMA AS [Schema]
,   c.TABLE_NAME AS [TableName]
,   c.COLUMN_NAME AS [ColumnName]
,   c.ORDINAL_POSITION AS [OrdinalPosition]
,   c.COLUMN_DEFAULT AS [ColumnDefault]
,   c.IS_NULLABLE AS [Nullable]
,   c.DATA_TYPE AS [DataType]
,   c.CHARACTER_MAXIMUM_LENGTH AS [CharacterMaxLength]
,   c.CHARACTER_OCTET_LENGTH AS [CharacterOctetLenth]
,   c.NUMERIC_PRECISION AS [NumericPrecision]
,   c.NUMERIC_PRECISION_RADIX AS [NumericPrecisionRadix]
,   c.NUMERIC_SCALE AS [NumericScale]
,   c.DATETIME_PRECISION AS [DatTimePrecision]
,   c.CHARACTER_SET_CATALOG AS [CharacterSetCatalog]
,   c.CHARACTER_SET_SCHEMA AS [CharacterSetSchema]
,   c.CHARACTER_SET_NAME AS [CharacterSetName]
,   c.COLLATION_CATALOG AS [CollationCatalog]
,   c.COLLATION_SCHEMA AS [CollationSchema]
,   c.COLLATION_NAME AS [CollationName]
,   c.DOMAIN_CATALOG AS [DomainCatalog]
,   c.DOMAIN_SCHEMA AS [DomainSchema]
,   c.DOMAIN_NAME AS [DomainName]
,   IsPrimaryKey = CONVERT(BIT, (SELECT
COUNT(*)
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
,   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
AND tc.TABLE_NAME = c.TABLE_NAME
AND cu.TABLE_SCHEMA = c.TABLE_SCHEMA
AND cu.COLUMN_NAME = c.COLUMN_NAME)
)
,   IsIdentity = CONVERT(BIT, (SELECT
COUNT(*)
FROM sys.objects obj
INNER JOIN sys.COLUMNS col
ON obj.object_id = col.object_id
WHERE obj.type = 'U'
AND obj.Name = c.TABLE_NAME
AND col.Name = c.COLUMN_NAME
AND col.is_identity = 1)
)
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE (@Schema IS NULL
OR c.TABLE_SCHEMA = @Schema)
AND (@TableName IS NULL
OR c.TABLE_NAME = @TableName)
";


var columns = databaseWrapper.Call(connection => connection.Query<SqlColumnInfo>(
sql: sql,
param: new { Schema = schema, TableName = table },
commandType: CommandType.Text)
.ToList());


var refs = this.GetReferentialConstraints(table: table, schema: schema);


foreach (var tableName in columns.Select(info => info.TableName).Distinct())
{
var tableColumns = columns.Where(info => info.TableName == tableName).ToList();
var children = refs.Where(c => c.UniqueTableName == tableName).ToList();
var parents = refs.Where(c => c.TableName == tableName).ToList();
result.Add(new SqlTableInfo
{
TableName = tableName,
Columns = tableColumns,
ChildConstraints = children,
ParentConstraints = parents
});


}


return result;
}


public List<SqlReferentialConstraint> GetReferentialConstraints(string table = null, string schema = null)
{
//https://technet.microsoft.com/en-us/library/aa175805%28v=sql.80%29.aspx
//https://technet.microsoft.com/en-us/library/Aa175805.312ron1%28l=en-us,v=sql.80%29.jpg
//https://msdn.microsoft.com/en-us/library/ms186778.aspx


var sql = @"
SELECT
KCU1.CONSTRAINT_NAME AS [ConstraintName]
,   KCU1.TABLE_NAME AS [TableName]
,   KCU1.COLUMN_NAME AS [ColumnName]
,   KCU2.CONSTRAINT_NAME AS [UniqueConstraintName]
,   KCU2.TABLE_NAME AS [UniqueTableName]
,   KCU2.COLUMN_NAME AS [UniqueColumnName]
,   RC.MATCH_OPTION AS [MatchOption]
,   RC.UPDATE_RULE AS [UpdateRule]
,   RC.DELETE_RULE AS [DeleteRule]
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
WHERE KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
AND (@Table IS NULL
OR KCU1.TABLE_NAME = @Table
OR KCU2.TABLE_NAME = @Table)
AND (@Schema IS NULL
OR KCU1.TABLE_SCHEMA = @Schema
OR KCU2.TABLE_SCHEMA = @Schema)
";


return databaseWrapper.Call(connection => connection.Query<SqlReferentialConstraint>(
sql: sql,
param: new { Table = table, Schema = schema },
commandType: CommandType.Text))
.ToList();
}


/// <summary>
///     Get Primary Key Column by schema and table name
/// </summary>
/// <param name="schema"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public string GetPrimaryKeyColumnName(string schema, string tableName)
{
var sql = @"SELECT
B.COLUMN_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
,   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.TABLE_NAME = @TableName
AND A.TABLE_SCHEMA = @Schema";


return databaseWrapper.Call(connection => connection.Query<string>(
sql: sql,
param: new { TableName = tableName, Schema = schema },
commandType: CommandType.Text))
.SingleOrDefault();
}


/// <summary>
///     Get Identity Column by table name
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public string GetIdentityColumnName(string tableName)
{
var sql = @"SELECT
c.Name
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
WHERE o.type = 'U'
AND c.is_identity = 1
AND o.Name = @TableName";


return databaseWrapper.Call(connection => connection.Query<string>(
sql: sql,
param: new { TableName = tableName },
commandType: CommandType.Text))
.SingleOrDefault();
}


/// <summary>
///     Get All Stored Procedures by schema
/// </summary>
/// <param name="schema"></param>
/// <param name="procName"></param>
/// <returns></returns>
public List<SqlStoredProcedureInfo> GetStoredProcedureInfo(string schema = null, string procName = null)
{
var result = new List<SqlStoredProcedureInfo>();


var sql = @"SELECT
SPECIFIC_NAME AS [Name]
,   SPECIFIC_SCHEMA AS [Schema]
,   Created AS [Created]
,   LAST_ALTERED AS [LastAltered]
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND (SPECIFIC_SCHEMA = @Schema
OR @Schema IS NULL)
AND (SPECIFIC_NAME = @ProcName
OR @ProcName IS NULL)
AND ((SPECIFIC_NAME NOT LIKE 'sp_%'
AND SPECIFIC_NAME NOT LIKE 'procUtils_GenerateClass'
AND (SPECIFIC_SCHEMA = @Schema
OR @Schema IS NULL))
OR SPECIFIC_SCHEMA <> @Schema)";


var sprocs = databaseWrapper.Call(connection => connection.Query<SqlStoredProcedureInfo>(
sql: sql,
param: new { Schema = schema, ProcName = procName },
commandType: CommandType.Text).ToList());


foreach (var s in sprocs)
{
s.Parameters = GetStoredProcedureInputParameters(sprocName: s.Name, schema: schema);
s.ResultColumns = GetColumnInfoFromStoredProcResult(storedProcName: s.Name, schema: schema);
result.Add(s);
}


return result;
}


/// <summary>
///     Get Column info from Stored procedure result set
/// </summary>
/// <param name="schema"></param>
/// <param name="storedProcName"></param>
/// <returns></returns>
public List<DataColumn> GetColumnInfoFromStoredProcResult(string schema, string storedProcName)
{
//this one actually needs to use the dataset because it has the only accurate information about columns and if they can be null or not.
var sb = new StringBuilder();
if (!String.IsNullOrEmpty(schema))
{
sb.Append(String.Format("exec [{0}].[{1}] ", schema, storedProcName));
}
else
{
sb.Append(String.Format("exec [{0}] ", storedProcName));
}


var prms = GetStoredProcedureInputParameters(schema, storedProcName);


var count = 1;
foreach (var param in prms)
{
sb.Append(String.Format("{0}=null", param.Name));
if (count < prms.Count)
{
sb.Append(", ");
}
count++;
}


var ds = new DataSet();
using (var sqlConnection = (SqlConnection)databaseWrapper.GetOpenDbConnection())
{
using (var sqlAdapter = new SqlDataAdapter(sb.ToString(), sqlConnection))
{
if (sqlConnection.State != ConnectionState.Open) sqlConnection.Open();


sqlAdapter.SelectCommand.ExecuteReader(CommandBehavior.SchemaOnly);


sqlConnection.Close();


sqlAdapter.FillSchema(ds, SchemaType.Source, "MyTable");
}
}


var list = new List<DataColumn>();
if (ds.Tables.Count > 0)
{
list = ds.Tables["MyTable"].Columns.Cast<DataColumn>().ToList();
}


return list;
}


/// <summary>
///     Get the input parameters for a stored procedure
/// </summary>
/// <param name="schema"></param>
/// <param name="sprocName"></param>
/// <returns></returns>
public List<SqlParameterInfo> GetStoredProcedureInputParameters(string schema = null, string sprocName = null)
{
var sql = @"SELECT
SCHEMA_NAME(schema_id) AS [Schema]
,   P.Name AS Name
,   @ProcName AS ProcedureName
,   TYPE_NAME(P.user_type_id) AS [ParameterDataType]
,   P.max_length AS [MaxLength]
,   P.Precision AS [Precision]
,   P.Scale AS Scale
,   P.has_default_value AS HasDefaultValue
,   P.default_value AS DefaultValue
,   P.object_id AS ObjectId
,   P.parameter_id AS ParameterId
,   P.system_type_id AS SystemTypeId
,   P.user_type_id AS UserTypeId
,   P.is_output AS IsOutput
,   P.is_cursor_ref AS IsCursor
,   P.is_xml_document AS IsXmlDocument
,   P.xml_collection_id AS XmlCollectionId
,   P.is_readonly AS IsReadOnly
FROM sys.objects AS SO
INNER JOIN sys.parameters AS P ON SO.object_id = P.object_id
WHERE SO.object_id IN (SELECT
object_id
FROM sys.objects
WHERE type IN ('P', 'FN'))
AND (SO.Name = @ProcName
OR @ProcName IS NULL)
AND (SCHEMA_NAME(schema_id) = @Schema
OR @Schema IS NULL)
ORDER BY P.parameter_id ASC";


var result = databaseWrapper.Call(connection => connection.Query<SqlParameterInfo>(
sql: sql,
param: new { Schema = schema, ProcName = sprocName },
commandType: CommandType.Text))
.ToList();


return result;
}

外部密钥元数据

小开

看看这个:

SELECT TABLE_SCHEMA ,
TABLE_NAME ,
COLUMN_NAME ,
ORDINAL_POSITION ,
COLUMN_DEFAULT ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
NUMERIC_PRECISION ,
NUMERIC_PRECISION_RADIX ,
NUMERIC_SCALE ,
DATETIME_PRECISION
FROM   INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA in ('dbo','meta')
and table_name in (select name from sys.tables)
order by TABLE_SCHEMA ,       TABLE_NAME ,ORDINAL_POSITION

enter image description here

小开

获得基本元数据摘要的最简单方法是使用一个临时表，然后使用 EXEC 函数:

SELECT * INTO #TempTable FROM TableName
EXEC [tempdb].[dbo].[sp_help] N'#TempTable'

对于表中的所有列，这将为您提供 栏名称,
资料类别、,
计算长度,
总统,
比例尺, 可取消,
TrimTrailingBlanks, 修正 LenNullInSource, 排序类别

小开

有两种简单的方法:

  • 选择一

    Sp _ help‘ schema.table _ name’

  • 选择二

    SELECT * FROM INformATION _ SCHEMA. column c WHERE c.table _ name = ‘ table _ name’


提交答案