There are several ways to document a database. This post will only discuss some of the queries you can run against your database to get back information about the objects in the database.

Tables

Here below is a SQL script that will give you the columns of a table. You just have to replace YourTableName at the bottom with the actual name of the table.

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('YourTableName')

Below is a modified version of the one above that uses SQL Server’s Case statement.

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision as 'Precision',
    c.scale as 'Scale',
    case when c.is_nullable = 0 then 'not null'
	else 'null' end as 'Nullable',
    case when ISNULL(i.is_primary_key, 0) = 0 then 'No'
	else 'Yes' end as 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('YourTableName')

Here is what the output generally looks like when you use the above query and substitute a table name at the bottom of the code.

Views

Below is a query you can run to get information about your views.

select V.name as 'View Name', S.text as 'View Text'
from sys.syscomments as S
inner join sys.views as V
on S.id = V.object_Id;

There are several ways to get a list of your views, functions and stored procedures. Here is another way.

select * from sys.sql_modules

Extended Properties

You can use SQL Server’s extended properties to document the database and the objects within it.

Here is another script that almost works perfectly except that we are getting unwanted duplicate rows whenever we have some text in the Description of the column of the table. I am not sure how to fix this, but it is a start.

select t.name as 'Table Name',
	c.name as 'Column Name',
    ty.Name as 'Data type',
    c.max_length as 'Max Length',
    c.precision as 'Precision',
    c.scale as 'Scale',
    case when c.is_nullable = 0 then 'not null'
	else 'null' end as 'Nullable',
    case when ISNULL(i.is_primary_key, 0) = 0 then 'No'
	else 'Yes' end as 'Primary Key',
	ep.value as 'Extended Property Value'
	from sys.tables as t
	LEFT JOIN
	    sys.columns c on t.object_id = c.object_id 
	INNER JOIN 
		sys.types ty ON c.user_type_id = ty.user_type_id
	LEFT OUTER JOIN 
		sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
	LEFT OUTER JOIN 
		sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
	LEFT OUTER JOIN sys.extended_properties  ep ON c.object_id = ep.major_id and 
	    c.object_id = t.object_id
order by t.name, c.name