Column

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