--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]