With this script you can retrieve column info (datatype, length, precision, scale and collation) from all tables in a specific database.
-- Retrieve column info from all tables in a database... USE [Databasename] GO -- Declare variables... DECLARE @vTableName AS nvarchar(256) -- Create temptable... CREATE TABLE #tDBColumnInfo ( [tablename] [varchar] (100) NULL, [columnname] [varchar] (100) NULL, [datatype] [varchar] (50) NULL, [length] [int] NULL, [xprec] [int] NULL, [xscale] [int] NULL, [collation] [varchar] (100) NULL ) -- Get Tablenames... DECLARE curTableNames CURSOR FOR SELECT [name] from sys.tables -- Get columns from table... OPEN curTableNames FETCH NEXT FROM curTableNames INTO @vTableName WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #tDBColumnInfo SELECT @vTableName AS 'tablename', sc.[name] AS [columnname], st.[name] AS [datatype], sc.[length], sc.[xprec], sc.[xscale], sc.[collation] FROM syscolumns sc LEFT OUTER JOIN systypes st on sc.xtype = st.xtype WHERE id = ( SELECT id FROM sysobjects WHERE [type] = 'U' AND [Name] = @vTableName ) FETCH NEXT FROM curTableNames INTO @vTableName END -- Cleanup cursor... CLOSE curTableNames DEALLOCATE curTableNames -- Display Result and cleanup temptable... SELECT * FROM #tDBColumnInfo DROP Table #tDBColumnInfo