A UDF to list column names of a SQL database table

Sunday, December 28, 2003

Use the INFORMATION_SCHEMA views to get column names and column properties of a table.

CREATE FUNCTION GetTableColumnInfo( 
    @TableName varchar(50)
)
RETURNS TABLE
AS
RETURN
   (SELECT ist.TABLE_CATALOG, 
           ist.TABLE_NAME,
           isc.COLUMN_NAME,
           isc.DATA_TYPE
   FROM INFORMATION_SCHEMA.TABLES ist
      INNER JOIN INFORMATION_SCHEMA.COLUMNS isc
      ON ist.TABLE_CATALOG = isc.TABLE_CATALOG
      AND ist.TABLE_SCHEMA = isc.TABLE_SCHEMA
      AND ist.TABLE_NAME = isc.TABLE_NAME
      AND ist.TABLE_TYPE = 'BASE TABLE'
   WHERE ist.TABLE_NAME = @TableName
      OR @TableName IS null)

To invoke this function type

    select * from GetTableColumnInfo ('tableName')

 

by Llama Lopon

by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!