September 7, 2012 at 4:12 am
I recently started with a new company and whilst getting to grips with databases and the systems etc i have had to start documentation of some of the DB's involved in a set of processes.
This involved me logging each table, each column, data types, foreign keys, primary keys, calc columns and what the calculation is and some other information. This was going to be a boring and long task. So i started work on some sql and after a couple hours i came up with the below sql which although could be improved, has helped me to document 2 db's in seconds once i had developed it..
Hope this can help some others
This script may not be 2012 specific but was written on 2012 so i cannot be sure it will all be backwards compatible..
UPDATE: this script will work in 2012/2008/2005 SQL Server
--=======================================================================================================================
--Author: Terry White
--Create Date: 07-09-2012
--Description:List all tables in a database, the tables schema
--The columns for the table, the data type of column, if it is a foreign Key and what table it is keyed to
--if the column is calculated shows the calculation and also if it is persisted
--also a few other bits related to the column
--=======================================================================================================================
select ss.Name as [Schema]
, so.Name as TableName
, sc.Name as ColumnName
, st.Name as DataType
, case when sc.max_Length = -1 then 'max' else cast( sc.max_Length as varchar) end as [Length]
, case when sc.max_length = -1 then st.name + ' (max)'
when st.Name like '%archa%' then st.name +' ('+cast( sc.Max_Length as varchar)+')'
when st.Name ='sysname' then st.name +' ('+cast( sc.Max_Length as varchar)+')'
else st.name
end as Datatype
,case when st.xtype = 56 then 0 else sc.precision end as [Precision]
,sc.scale
,case when sc.is_nullable = 1 then 'Y' else 'N' end as [Nullable]
,case when sc.is_identity = 1 then 'Y' else 'N' end as [Identity]
,case when sc.is_computed = 1 then 'Y' else 'N' end as [Computed]
,case when sc.is_ansi_padded = 1 then 'Y' else 'N' end as [AnsiPadded]
,case when rfk.column_id is not null then 'FK'
when pks.pkcolumn is not null then 'PK'
else '' end as [Key?]
,case when rfk.column_id is not null then rfk.FKSchema+'.'+rfk.FKTable +'.'+rfk.fkcolumn else '' end as [FK To]
,case when cc.object_id is not null then definition else '' end as CalculationDefinition
,case when cc.object_id is not null and cc.is_persisted = 1 then 'Y'
when cc.object_id is not null and cc.is_persisted = 0 then 'N'
else '' end as [Persisted]
from sys.objects so
left join sys.columns sc on sc.Object_id = so.object_ID
left join sys.systypes st on st.xtype = sc.system_type_id
left join sys.schemas ss on ss.schema_id = so.schema_id
left join sys.sysforeignkeys sfk on sfk.fkeyid = so.object_id and sfk.fkey = sc.column_id
left join ( select iso.object_id, isc.column_id, iso.Name as FKTable, isc.Name as FKColumn, iss.name as FKSchema
from sys.objects iso
join sys.columns isc on isc.Object_id = iso.object_ID
join sys.schemas iss on iss.schema_id = iso.schema_id
) rfk on rfk.object_id = sfk.rkeyid and rfk.column_id = sfk.rkey
left join ( SELECT distinct kc.name
,c.NAME as PKColumn
,c.object_id
,so.name as TableName
,ss.name as SchemaName
,so.schema_id
FROM sys.key_constraints kc
join sys.index_columns ic ON kc.parent_object_id = ic.object_id and kc.unique_index_id = ic.index_id
join sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
join sys.objects so on c.Object_id = so.object_ID
join sys.schemas ss on ss.schema_id = so.schema_id
WHERE kc.type = 'PK'
) pks on pks.pkcolumn = sc.name and pks.object_id = sc.object_id and pks.schema_id = so.schema_id
left join sys.computed_columns cc on cc.object_id = sc.object_id and cc.column_id = sc.column_id
where so.type = 'U'
and st.status = 0
order by so.Name, ss.name
September 7, 2012 at 5:18 am
Nice Job...
Thanks,
Sumit:-)
September 21, 2012 at 4:22 am
It runs in SQL 2008.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 21, 2012 at 5:37 am
dwain.c (9/21/2012)
It runs in SQL 2008.
runs on 2005 also;
Lowell
September 21, 2012 at 6:39 am
Thanks for the feedback people. Post updated to include the other SQL versions in the main post.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply