Cool Tips‎ > ‎Data Type‎ > ‎

Data Type - Get Column Names and Data Types

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

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]


ċ
usp_GetColumnNamesandDataTypes.sql
(1k)
Andy Hughes,
Jun 23, 2012, 1:38 AM
Comments