Technical Article

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

,

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

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (2)

You rated this post out of 5. Change rating

Related content