T-SQL query to show table definition?

Visit http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt.

You will find the code of sp_getddl procedure for SQL Server. The purpose of the procedure is script any table, temp table or object.

USAGE:

exec sp_GetDDL GMACT

or

exec sp_GetDDL 'bob.example'

or

exec sp_GetDDL '[schemaname].[tablename]'

or

exec sp_GetDDL #temp

I tested it on SQL Server 2012, and it does an excellent job.

I'm not the author of the procedure. Any improvement you make to it send to Lowell Izaguirre ([email protected]).

The easiest and quickest way I can think of would be to use sp_help

sp_help 'TableName'

Have you tried sp_help?

sp_help 'TableName'

There is no easy way to return the DDL. However you can get most of the details from Information Schema Views and System Views.

SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
       , IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Customers'

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
WHERE TABLE_NAME = 'Customers'

SELECT name, type_desc, is_unique, is_primary_key
FROM sys.indexes
WHERE [object_id] = OBJECT_ID('dbo.Customers')