Data Dictionary for SQL Server

Every now and then I face this scenario where I am required to provide a quick Data Dictionary in a spreadsheet.This spreadsheet needs to have just the list of all tables and their properties.This is not one of the cases where the comments are required, rather a quick view to just skim thru the table list or for somebody to be aware of what table structures are there is the database.


I was able to come up with a query to consolidate all the required information and to format it so that it can be copied to a spreadsheet and sent out.


select 
case when clm.column_id = 1 then object_schema_name(clm.object_id) else '' end as 'Schema',
case when clm.column_id = 1 then object_name(clm.object_id) else '' end as 'Table',
clm.name as 'Column',
typ.name as 'Datatype',
clm.max_length as 'Length',
case   
when typ.name in ('int','smallint','decimal','money','tinyint','date','smallmoney','time','numeric','float','real','bigint','datetime2','datetimeoffset')  
then ltrim(rtrim(str(clm.precision))) +',' + ltrim(rtrim(str(clm.scale))) 
else ''  
end as 'Precision,Scale',
clm.is_nullable as 'Nullability',
case 
when cns.definition is null 
then '' 
/* below condition not reqd for SQL 2000 database upgraded to later versions*/
when isnumeric(substring(cns.definition,3,len(cns.definition)-4)) = 1 
then substring(cns.definition,3,len(rtrim(cns.definition))-4) --2005 onwards
else substring(cns.definition,2,len(cns.definition)-2)  --for 2000 database upgraded to later versions 
end as 'Default'
from sys.columns clm join sys.types typ 
  on clm.user_type_id = typ.user_type_id
left join sys.default_constraints cns
  on clm.default_object_id = cns.object_id
where objectpropertyex ( clm.object_id , 'IsUserTable' ) = 1
order by object_name(clm.object_id),clm.column_id





When I got the defaults to be displayed I could see that SQL Server 2005 onward there were two sets of parentheses around the default value especially for numeric datatypes as against just one for character default values. Whereas for SQL Server 2000 databases upgraded to later versions, there was an uniform parentheses around the default value.
I added a condition to check if the datatype being processed is numeric so that the query works across SQL Server 2005/2008/2012.
For SQL 2000 database upgraded to later versions just comment out the 



Also I was not sure if I had to be using the system_type_ids or user_type_ids while getting the datatypes.
The user_type_ids are distinct whereas for certain datatypes system_type_ids are repetitive.So I have used the user_type_ids to get the datatypes.


I have also displayed precision and scale for the datypes where this information is relavent and blanks for the rest.


--Aravind