Decrease Font Size
Increase Font Size
   BLOG

List columns and data types of all tables in a SQL server 2005/2008

by bryian 1. September 2009 20:01

List Columns And Data Types In Sql Server

How to list my SQL table column/field names?

List columns and attributes for every table in a SQL Server database

List down the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 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
GO

Tags: , , , ,

SQL