Below query can be used to check table definition in SQL Server

select db_name() as 'DatabaseName',
ss.name+'.'+so.name as ObjectName,
so.type_desc as ObjectType,
sc.name as ColumnName

    when st.name IN ('char','varchar') then st.name+'('+case when sc.max_length<0 then 'MAX' else convert(varchar(10),sc.max_length) END+')'
    when st.name IN ('nvarchar','nchar') then st.name+'('+case when sc.max_length<0 then 'MAX' else convert(varchar(10),sc.max_length/2) END+')'
    when st.name IN ('numeric') then st.name+'('+convert(varchar(10),sc.precision)+','+convert(varchar(10),sc.scale)+')'
    else st.name
end as DataType
     when sc.is_nullable=1 then 'Null'
    else 'Not Null'
end as Nullable    
             when ic.column_id is null then ''
             else ' identity('+isnull(convert(varchar(10),ic.seed_value),'')+','+isnull(convert(varchar(10),ic.increment_value),'')+')='+isnull(convert(varchar(10),ic.last_value),'null')
             when sccm.column_id is null then ''
             else ' computed('+isnull(sccm.definition,'')+')'
             when scc.object_id is null then ''
             else ' check('+isnull(scc.definition,'')+')'
            as ExtraInfo

from sys.columns sc
inner join sys.types st on sc.system_type_id=st.user_type_id and st.is_user_defined=0
inner join sys.objects so on sc.object_id=so.object_id
inner join sys.schemas ss on so.schema_id=ss.schema_id
left outer join sys.identity_columns  ic on sc.object_id=ic.object_id and sc.column_id=ic.column_id
left outer join sys.computed_columns  sccm on sc.object_id=sccm.object_id and sc.column_id=sccm.column_id
left outer join sys.check_constraints scc on sc.object_id=scc.parent_object_id and sc.column_id=scc.parent_column_id
where so.name in
   (select table_name from information_schema.tables where table_name='BlkScenario' ) -- change table name here
order by ss.name+'.'+so.name,sc.column_id


How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.