Simple Schema Output for MSSQL

SimpleSchema.sql

SELECT
	s.name [Schema Name],
	tables.name [Table Name],
    c.name [Column Name],
	ISNULL(i.is_primary_key, 0) [Is Primary Key],
    t.Name [Data type],
    c.max_length [Max Length],
    c.precision ,
    c.scale ,
    c.is_nullable,
	sc.text [Default Value]
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_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.tables tables ON tables.object_id = c.object_id
LEFT OUTER JOIN
	sys.schemas s ON tables.schema_id = s.schema_id
LEFT OUTER JOIN
	sys.syscomments sc ON c.default_object_id = sc.id
WHERE
    s.name IN ('DW', 'Audit', 'dbo', 'Staging','LKP')
ORDER BY 1,2,4 DESC,3,5,6,7,8,9,10


SELECT * FROM INFORMATION_SCHEMA.VIEWS
ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, CHECK_OPTION, IS_UPDATABLE


SELECT 
s.name
,obj.Name as SPName
,modu.definition as SPDefinition
FROM sys.sql_modules modu
INNER JOIN 
	sys.objects obj ON modu.object_id = obj.object_id
LEFT OUTER JOIN
	sys.schemas s ON obj.schema_id = s.schema_id
WHERE obj.type = 'P'
ORDER BY 1,2,3

This is a quick schema output useful in comparing two databases. Usually I use this for comparing a current DB to what can be scripted out in source control.

This does tables, views, and procedures....TO DO needed for indexes partitions, and lots of other DB fun!

for varchar(4) this returns 8