Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In SQL Server 2008 - is there any script available which will list all tables (incl column names, data type, length) for the entire database - with one script.

share|improve this question
add comment

1 Answer

You can use below script :

SELECT QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'SchemaName'
    ,QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'TableName'
    ,C.NAME AS 'ColumnName'
    ,T.NAME AS 'DataType'
    ,C.max_length
    ,C.is_nullable
FROM SYS.COLUMNS C
INNER JOIN SYS.TABLES tb ON tb.[object_id] = C.[object_id]
INNER JOIN SYS.TYPES T ON C.system_type_id = T.user_type_id
WHERE tb.[is_ms_shipped] = 0
ORDER BY tb.[Name]

For Complete Documenting sql server, I highly recommend :

SQL Server & Windows Documentation Using Windows PowerShell written by Kendal Van Dyke

Brief description from the link :

SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations. SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. SQL Power Doc is also capable of documenting Windows Azure SQL Databases.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.