List down the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database

IF EXISTS (select * from dbo.sysobjects where id =
object_id(N'[dbo].[UTILfn_Split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[UTILfn_Split]
GO

create function dbo.UTILfn_Split(
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)

--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)

--Check for trailing Comma, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
set @String = @String + @Delimiter

--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1

--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)

insert into @ValueTable ( [Value]) Values (@NextString)

set @String = substring(@String,@pos +1,len(@String))

set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end

return
end
--------------------------------------------
/*
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 -- Util_ListDBSchema 'ServiceMatchMaker','*'
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

Posted in: SQL,SQL_UTILITY_SPs