Database Layout - SQL Server

The following T-SQL script will return a record for every column in every table of the current database. Each record will show the definition of the column, including its name, data type, nullability, whether it's an identity column or part of the primary key, and what its order is in the table definition.

SQL Server 2005¶

This version also includes the default values and description for each column, where defined. Note that the fn_listextendedproperty function, used for the ColumnDesc column, requires a database compatibility level of at least 90.

/*==================================================================================================
    OBJECT: DbLayout view
    SOURCE: http://www.jasinskionline.com/TechnicalWiki/Database-Layout-SQL-Server.ashx
==================================================================================================*/
create view dbo.DbLayout
as
select distinct

     DatabaseName   = db_name()
    ,SchemaName     = s.name
    ,TableName      = o.name 
    ,ColumnName     = c.name

    ,DataType       = case 
                        when t.name in ('char','text','varchar')

                            then t.name + '(' + case when c.max_length = 0 then 'max' else convert(varchar,c.max_length) end + ')'

                        when t.name in ('nchar','ntext','nvarchar')

                            then t.name + '(' + case when c.max_length = 0 then 'max' else convert(varchar,c.max_length/2) end + ')'

                        when t.name in ('decimal','numeric','real')

                            then t.name + '(' + convert(varchar,c.precision) + ',' + 
                                                convert(varchar,c.scale) + ')'

                        else
 
                            t.name 

                        end

    ,IsNullable     = c.is_nullable 
    ,IsIdentity     = c.is_identity
    
    ,IdentityDesc   = case when c.is_identity = 0 then '' else '(' 
                        + convert(varchar(50), IDENT_SEED(o.name)) 
                        + ', ' 
                        + convert(varchar(50), IDENT_INCR(o.name))
                        + ')' 
                        end

    ,IsPkey         = case when c.column_id in 
                        (select k.colid from 
                            sysindexes i 
                            inner join sysindexkeys k on i.indid = k.indid and i.id = k.id
                            inner join sysobjects o2 on i.id = o2.id 
                            inner join syscolumns c2 on k.id = c2.id and k.colid = c2.colid
                        where (i.status & 0x800) = 0x800
                            and o2.id = o.object_id) then 1 else 0 end

    ,ColumnOrder    = c.column_id

    ,DefaultValue   = coalesce((select definition 
                                from sys.default_constraints 
                                where object_id = c.default_object_id), '')

    ,ColumnDesc     = coalesce((
                        select value 
                        from fn_listextendedproperty('MS_Description', 'schema', s.name, 
                                'table', o.name, 'column', c.name) 
                        ), '')

    ,TableDesc      = coalesce((
                        select value 
                        from fn_listextendedproperty('MS_Description', 'schema', s.name, 
                            'table', o.name, default, default) 
                        ), '')

    ,ObjectType     = o.Type

from 
    sys.objects o

    inner join sys.columns c 
        on o.object_id = c.object_id

    inner join sys.types t 
        on t.system_type_id = c.system_type_id

    inner join sys.schemas s
        on o.schema_id = s.schema_id

where 1=1
    and o.type = 'u'
    and t.name not in ('sysname')
    and o.name not in ('dtproperties')

order by 
     TableName
    ,SchemaName
    ,ColumnOrder
go
create view dbo.SimpleDbLayout
as
select 
     [Table Name]   = TableName
    ,[Column Name]  = ColumnName
    ,[Data Type]    = DataType
    ,Nullable       = case IsNullable when 1 then 'null' else 'not null' end
    ,PK             = case IsPkey when 1 then 'PK' else '' end + IdentityDesc
    ,Seq            = ColumnOrder
from 
    dbo.DbLayout 
where 1=1
    and ObjectType = 'u'

SQL Server 2000¶

select distinct
     DatabaseName   = db_name()
    ,SchemaName     = 'dbo'
    ,TableName      = o.name 
    ,ColumnName     = c.name

    ,DataType       = case 
                        when t.name in ('char','text','varchar')

                            then t.name + '(' + convert(varchar,c.max_length) + ')'

                        when t.name in ('nchar','ntext','nvarchar')

                            then t.name + '(' + convert(varchar,c.max_length/2) + ')'

                        when t.name in ('decimal','numeric','real')

                            then t.name + '(' + convert(varchar,c.precision) + ',' + 
                                                convert(varchar,c.scale) + ')'

                        else
 
                            t.name 

                        end

    ,IsNullable     = convert(bit, c.isnullable)

    ,IsIdentity     = convert(bit, case when c.status & 128 > 0 then 1 else 0 end)
    
    ,IsPkey         = convert(bit, case when c.colid in (
                            select 
                                k.colid 
                            from 
                                sysindexes i 
                                inner join sysindexkeys k 
                                    on i.indid = k.indid and i.id = k.id
                                inner join sysobjects o2 
                                    on i.id = o2.id 
                                inner join syscolumns c2 
                                    on k.id = c2.id and k.colid = c2.colid
                            where 1=1
                                and (i.status & 0x800) = 0x800
                                and o2.id = o.id
                            ) then 1 
                        else 0 
                        end)

    ,ColumnOrder = c.colorder

    ,DefaultValue   = coalesce((
                        select distinct
                            c3.text 
                        from 
                            sysconstraints c2
                            inner join sysobjects d
                                on c2.constid = d.id
                                and d.type = 'D'
                            inner join sysobjects t
                                on c2.id = t.id
                            inner join syscolumns c4
                                on c2.colid = c4.colid
                                and t.id = c4.id
                            inner join syscomments c3
                                on c3.id = d.id
                        where 1=1
                            and t.type = 'u'
                        ), '')
from 
    sysobjects o

    inner join syscolumns c 
        on o.id = c.id

    inner join systypes t 
        on t.xtype = c.xtype

where 1=1
    and o.type = 'u'
    and t.name not in ('sysname')
    and o.name not in ('dtproperties')
order by 
     o.name
    ,c.colorder