Skip to content

Instantly share code, notes, and snippets.

@kmoormann
Created September 5, 2012 01:45
Show Gist options
  • Save kmoormann/3629001 to your computer and use it in GitHub Desktop.
Save kmoormann/3629001 to your computer and use it in GitHub Desktop.
Simple Schema Output for MSSQL
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
@kmoormann
Copy link
Author

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

@florinbuda85
Copy link

for varchar(4) this returns 8

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment