Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

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 + '(' + 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','float','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(NULL, 'schema', s.name, 
                                'table', o.name, 'column', c.name) 
                        where name='MS_Description'
                        ), '')

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

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','float','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