SQL Server: retrieving table metadata (description, fields and their data types)

Question

I am trying to find a way to retrieve information about my tables in SQL Server (2008).
The necessary data should include a description of the table (filled out from the Description property in the properties window), a list of the fields of this table and their corresponding data types .

Is there any way to extract such metadata? I assume that I need to use some sys sp, but I'm not sure which one.

Answer 1

To get the description data, you unfortunately must use sysobjects / syscolumns to get the identifiers:

 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 

You can do this with info-schema, but you will need to concatenate, etc. to call OBJECT_ID () - so what will be the point?

Answer 2

General information about tables and columns is contained in the following tables:

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

The table description is an extended property, you can request them from sys.extended_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' 

Answer 3

You can try sp_help <Name of object>

Answer 4

I liked @Andomar's answer better, but I also need column descriptions. Here is his request has been modified to include them as well. (Uncomment the last part of the WHERE clause to return only strings where any description is not null).

 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 

Answer 5

Using object catalog views:

 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'; 

Using Information Schemes

 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 

Answer 6

If you pull your queries using java code, there is a great class that you can use ResultSetMetaData that can extract column names and column properties (type and length).

Example

 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)); } } 

Answer 7

Depending on how much metadata you want, this works for me: where could there be anything you want, like Northwind.dbo.Products or just products

 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 

Answer 8

I use this SQL code to get all the column information.

 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' 

Answer 9

Check this:

 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 

Answer 10

If you just want to view information in a convenient way, Red Gate SQL Prompt can help.

If you hover over the text of the object in the query window, SQL Prompt displays the text of the extended MS_Description property in the tooltip. When you click on the tooltip, a dialog box opens with information about the columns, as well as the DDL of the object.

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

Answer 11

If using .NET code, I would suggest using SMO: http://msdn.microsoft.com/en-us/library/ms162169.aspx . In your specific case, this would be a table class http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.table.aspx This would be a more portable solution than using system views and tables with a specific version.

If this is what you intend to use on a regular basis, you can write a simple console application, perhaps with a T4 <a2> runtime code generator

If this is just a one-time job, you can use my LiveDoco ( http://www.livedoco.com ) export to an XML function with the optional XSLT conversion, or I'm sure there are free tools out there that can do this. This looks fine: http://sqldbdoc.codeplex.com/ - supports XML through XSLT, but I'm not sure if you can run it to select tables, though (With LiveDoco you can).

Answer 12

  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' 

Answer 13

 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 

Answer 14

I just finished the .net library with a few useful queries that return strongly typed C # objects for gen / t4 code templates.

nuget SqlMeta

Project site

github source

 /// <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; } 

Foreign key metadata

Answer 15

The easiest way to get a basic summary of metadata is to use a temporary table and then use the EXEC function:

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

For all columns in the table, this will give you the column name,
Data type
Estimated Length
Prec
Scale, Nullable,
TrimTrailingBlanks, FixedLenNullInSource, sort type