SQL Server: Extract Table Meta-Data (Description, Fields and Their Data Types)

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

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?

Where do I find Sql Server metadata for column datatypes?

You are close. You can look at sys.columns to get the columns.

You can filter on a table with OBJECT_ID=OBJECT_ID('dbo.Foo').

You can get the length from sys.columns. The data type is in the user_type_id field. The keys for that field are in sys.types.

In its entirety you can do:

select object_NAME(c.object_id), c.name, t.name, c.max_length
from sys.columns c
INNER JOIN sys.types t
    ON t.user_type_id = c.user_type_id

As a side note, in SQL Server the system tables are deprecated (i.e. syscolumns, sysobjects) and it's recommended as a best practice to use the views instead, sys.columns, sys.objects, etc.

This will give you Table, column, data type, and maxlength for each one.

SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints

To avoid duplicate rows for some columns, use user_type_id instead of system_type_id.

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('YourTableName')

Just replace YourTableName with your actual table name - works for SQL Server 2005 and up.

In case you are using schemas, replace YourTableName by YourSchemaName.YourTableName where YourSchemaName is the actual schema name and YourTableName is the actual table name.

SQL Server query to get data type for all columns in table?

SELECT c.name,
       c.max_length,
       c.precision,
       c.scale,
       c.is_nullable,
       t.name
  FROM sys.columns c
  JOIN sys.types   t
    ON c.user_type_id = t.user_type_id
 WHERE c.object_id    = Object_id('dbo.testtable')

SQL statement to get column type

Using SQL Server:

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_SCHEMA = 'yourSchemaName' AND
     TABLE_NAME   = 'yourTableName' AND 
     COLUMN_NAME  = 'yourColumnName'

---

Related Topics