List columns data type and size for every table in a database

brettington, 2009-09-15 (first published: 2009-09-02)

A couple days ago, my co-worker had asked if I could provide a list of all the columns in certain tables in some of our databases to share with a vendor. This vendor needed to know the column names, data type and max length, of each of the columns in the database. If you happen to come across this kind of situation, here is a stored procedure that will help you list the data type and size of all the columns of the selected or all the tables in a SQL Server 2005/2008 Database.

Here is the link to download the function UTILfn_Split

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: BT -- Create date: 08312009 -- Description: list table name, column name, data type… /* Util_ListDBSchema N'MyDatabase', N'tbl_Class,MyTempTable1' –several tables Util_ListDBSchema N'MyDatabase', N'*' –-all tables Util_ListDBSchema N'MyDatabase', N'tbl_movies' –one table */-- ============================================= CREATE PROCEDURE dbo.Util_ListDBSchema ( @dbName varchar(100), @tableName varchar(500) ) AS BEGIN SET NOCOUNT ON; DECLARE @strSql varchar(2500), @strWhere varchar(1000) --list tables only SET @strWhere = 'objectproperty(object_id(table_name),''IsTable'') = 1' IF (@tableName <> '*') SET @strWhere = @strWhere + 'and table_name in (SELECT * from dbo.UTILfn_Split ('''+@tableName+''','',''))' SET @strSQL = N' USE ['+ @dbName +'] SELECT table_catalog, table_schema, table_name, column_name, data_type, character_maximum_length as max_length FROM information_schema.columns WHERE '+@strWhere+' ORDER BY table_name, column_name ' -- print @strSQL exec (@strSQL) END