Get Column Names and Data Types

--exec dbo.usp_GetColumnNamesandDataTypes

--exec dbo.usp_GetColumnNamesandDataTypes 'DimAccount'

--exec dbo.usp_GetColumnNamesandDataTypes 'dbo.DimAccount'

DROP PROCEDURE dbo.usp_GetColumnNamesandDataTypes

GO

CREATE PROCEDURE dbo.usp_GetColumnNamesandDataTypes @TableName SYSNAME = NULL

AS

/**********************************************************

Purpose: To produce a friendly list of column names and

datatypes for all columns in the specified table(s)

in the current database.

A null value for the parameter @TableName will

bring back data for all tables

Author: ChillyDBA

History: 14 Dec 2010 - Initial Issue

***********************************************************/

SELECT

so.[Name] AS TableName,

sc.[Name] AS ColumnName,

sc.[colid] AS ColumnOrder,

st.[Name] AS DataTypeName,

sc.[Length] AS DataTypeLength,

sc.[xprec] AS DataTypePrecision,

sc.[xScale] AS DataTypeScale

FROM SysObjects so (NOLOCK)

INNER JOIN SysColumns sc (NOLOCK)

ON so.[Id] = sc.[Id]

INNER JOIN SysTypes st(NOLOCK)

ON st.[xtype] = sc.[xtype]

WHERE so.[type] = 'U'

AND st.[Name] <> 'sysname'

AND so.id = ISNULL(OBJECT_ID(@TableName), so.id)

ORDER BY so.[Name], sc.[ColID]