SQL Server Extract Table Meta-Data description fields and their data types

Question

I am trying to find a way to extract information about my tables in SQL Server  2008   The data I need needs to include the description of the table  filled from the Description property in the Properties Window   a list of fields of that table and their respective data types   Is there any way I can extract such meta-data   I presume I have to use some sys sp but I n not sure which one

User · Answer

If it is OK to use  NET code I d suggest using SMO  http   msdn microsoft com en-us library ms162169 aspx  In your particular case it would be the Table class http   msdn microsoft com en-us library microsoft sqlserver management smo table aspx This would be a more portable solution than using version specific system views and tables   If this is something you are going to use on a regular basis - you might want to write a simple console application  perhaps with a runtime T4 code generator http   msdn microsoft com en-us library ee844259 aspx  If it s just a one-off task - you could use my LiveDoco s  http   www livedoco com   export to XML feature with an optional XSLT transform or I m sure there are free tools out there that can do this   This one looks okay  http   sqldbdoc codeplex com  - supports XML via XSLT  but I m not sure if you can run it for a selection of tables though  With LiveDoco you can

User · Answer

Depending on how much metadata you want  this is working for me  where  could be the whole thing 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   lt table to inspect gt    ORDER BY column id

User · Answer

If you are pulling your queries using java code  there is a great class that can be used  ResultSetMetaData  that can retrieve column names and the properties of the columns  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  lt   rsmd getColumnCount    i                          System out println  column name                                  rsmd getColumnName i                        System out println  column size                                  rsmd getColumnDisplaySize i

User · Answer

Easiest way to get basic metadata summary is to use a temp table and then use EXEC function   SELECT   INTO  TempTable FROM TableName EXEC  tempdb   dbo   sp help  N  TempTable    For all columns in the table  this will give you  Column Name  Data Type  Computed Length  Prec  Scale   Nullable  TrimTrailingBlanks   FixedLenNullInSource   Collation Type

User · Answer

I just finished a  net library with a few useful queries that return strongly typed C  objects for code gen  t4 templates   nuget SqlMeta  Project Site  github source       lt summary gt              Get All Table Names          lt  summary gt           lt returns gt  lt  returns gt      public List lt string gt  GetTableNames                 var sql     SELECT name                     FROM dbo sysobjects                     WHERE xtype    U                       AND name  lt  gt   sysdiagrams                      order by name asc            return databaseWrapper Call connection   gt  connection Query lt string gt               sql  sql                ToList                    lt summary gt              Get table info by schema and table or null for all          lt  summary gt           lt param name  schema  gt  lt  param gt           lt param name  table  gt  lt  param gt           lt returns gt  lt  returns gt      public List lt SqlTableInfo gt  GetTableInfo string schema   null  string table   null                var result   new List lt SqlTableInfo gt              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   gt  connection Query lt SqlColumnInfo gt               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   gt  info TableName  Distinct                          var tableColumns   columns Where info   gt  info TableName    tableName  ToList                var children   refs Where c   gt  c UniqueTableName    tableName  ToList                var parents   refs Where c   gt  c TableName    tableName  ToList                result Add new SqlTableInfo                               TableName   tableName                  Columns   tableColumns                  ChildConstraints   children                  ParentConstraints   parents                                     return result             public List lt SqlReferentialConstraint gt  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   gt  connection Query lt SqlReferentialConstraint gt               sql  sql              param  new   Table   table  Schema   schema                commandType  CommandType Text                ToList                    lt summary gt              Get Primary Key Column by schema and table name          lt  summary gt           lt param name  schema  gt  lt  param gt           lt param name  tableName  gt  lt  param gt           lt returns gt  lt  returns gt      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   gt  connection Query lt string gt               sql  sql              param  new   TableName   tableName  Schema   schema                commandType  CommandType Text                SingleOrDefault                    lt summary gt              Get Identity Column by table name          lt  summary gt           lt param name  tableName  gt  lt  param gt           lt returns gt  lt  returns gt      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   gt  connection Query lt string gt               sql  sql              param  new   TableName   tableName                commandType  CommandType Text                SingleOrDefault                    lt summary gt              Get All Stored Procedures by schema          lt  summary gt           lt param name  schema  gt  lt  param gt           lt param name  procName  gt  lt  param gt           lt returns gt  lt  returns gt      public List lt SqlStoredProcedureInfo gt  GetStoredProcedureInfo string schema   null  string procName   null                var result   new List lt SqlStoredProcedureInfo gt              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  lt  gt   Schema             var sprocs   databaseWrapper Call connection   gt  connection Query lt SqlStoredProcedureInfo gt               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                  lt summary gt              Get Column info from Stored procedure result set          lt  summary gt           lt param name  schema  gt  lt  param gt           lt param name  storedProcName  gt  lt  param gt           lt returns gt  lt  returns gt      public List lt DataColumn gt  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  lt  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 lt DataColumn gt             if  ds Tables Count  gt  0                        list   ds Tables  MyTable   Columns Cast lt DataColumn gt    ToList                       return list                  lt summary gt              Get the input parameters for a stored procedure          lt  summary gt           lt param name  schema  gt  lt  param gt           lt param name  sprocName  gt  lt  param gt           lt returns gt  lt  returns gt      public List lt SqlParameterInfo gt  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   gt  connection Query lt SqlParameterInfo gt               sql  sql              param  new   Schema   schema  ProcName   sprocName                commandType  CommandType Text                ToList             return result          Foreign Key Metadata

User · Answer

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

User · Answer

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 Schema Views  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

User · Answer

Generic information about tables and columns can be found in these tables   select   from INFORMATION SCHEMA TABLES select   from INFORMATION SCHEMA COLUMNS   The table description is an extended property  you can query 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

User · Answer

Check this out   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

User · Answer

There are 2 simple ways   Option 1 sp help  schema table name   Option 2 SELECT   FROM INFORMATION SCHEMA columns c WHERE c table name    table name

User · Answer

I use this SQL code to get all the information about a column   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

User · Answer

You could try sp help  lt Name of object gt

User · Answer

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

User · Answer

To get the description data  you unfortunately have to use sysobjects syscolumns to get the ids   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 it with info-schema  but you d have to concatenate etc to call OBJECT ID   - so what would be the point

User · Answer

I liked  Andomar s answer best  but I needed the column descriptions also  Here is his query modified to include those also   Uncomment the last part of the WHERE clause to return only rows where either description is non-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

User · Answer

If you simply want to view the information in a convenient way  Red Gate s SQL Prompt might help    If you hover over the object text in a query window SQL Prompt will display the MS Description extended property text in a tooltip  Clicking on the tooltip will open a dialog displaying the column information and also the object s DDL   http   www red-gate com products sql-development sql-prompt

[sql] SQL Server: Extract Table Meta-Data (description, fields and their data types)

Examples related to sql

Examples related to sql-server

Examples related to metadata