identity
fn_listextendedproperty
ColumnDesc
/*================================================================================================== 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'
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
ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.