Auto Documentation of database

Now u need not to write documentation in notebooks or by typeing all the tables names and fields names and their details in ms word...just by this single functiion u can easly get complete documentation of your current opened database...


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[GetDataBaseDetail]() returns table
as
return(
SELECT top 10000 c.TABLE_NAME 'Table Name',c.column_name 'Column Name',
data_type 'Data Type',
CHARacter_maximum_length 'Maximum Length',
IS_NULLABLE 'Allow Null',
tc.CONSTRAINT_NAME 'Constraint Name'
,s.value 'Description'
FROM information_schema.columns c
left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE tc on c.COLUMN_NAME = tc.COLUMN_NAME
left join sys.extended_properties s on s.major_id = OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME)
AND s.minor_id = c.ORDINAL_POSITION
AND s.name = 'MS_Description'
inner join INFORMATION_SCHEMA.TABLES tbl
on tbl.TABLE_NAME = c.TABLE_NAME
and tbl.TABLE_TYPE = 'BASE TABLE'

group by c.TABLE_NAME ,tc.CONSTRAINT_NAME,c.COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH ,IS_NULLABLE,s.value
order by c.TABLE_NAME ,tc.CONSTRAINT_NAME desc
)