Credits:
Author: ChillyDBA
Date: 14 Dec 2010
Description
A small SP to retrieve just the column names and basic data types for one or all tables in the current database.
Code
--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]
|
 Updating...
Andy Hughes, Jun 23, 2012, 1:38 AM
|