Table definition from information_schema

USE [fp_r4_phoenixwsd]  — Specify your target database

— Get Table names from INFORMATION_SCHEMA.TABLES

SELECT table_name

FROM     information_schema.tables

WHERE    (table_type = ‘BASE TABLE’)

ORDER BY table_name

— Get TABLE Definition

DECLARE  @TABLE NVARCHAR(50)

SET @TABLE = ‘tbl_WM_JobReport’  — Specify Table Name

SELECT column_name,

 CASE data_type

    WHEN ‘INT’ THEN data_type

    WHEN ‘TINYINT’ THEN data_type

    WHEN ‘SMALLINT’ THEN data_type

    WHEN ‘BIT’ THEN data_type

    WHEN ‘DATETIME’ THEN data_type

    WHEN ‘SMALLDATETIME’ THEN data_type

    WHEN ‘UNIQUEIDENTIFIER’ THEN data_type

    WHEN ‘MONEY’ THEN data_type

    WHEN ‘SMALLMONEY’ THEN data_type

    WHEN ‘FLOAT’ THEN data_type

    WHEN ‘NTEXT’ THEN data_type

    WHEN ‘IMAGE’ THEN data_type

    WHEN ‘NVARCHAR’ THEN data_type + ‘(‘ + Convert(NVARCHAR(8),character_maximum_length) + ‘)’

    WHEN ‘VARCHAR’ THEN data_type + ‘(‘ + Convert(NVARCHAR(8),character_maximum_length) + ‘)’

    WHEN ‘NCHAR’ THEN data_type + ‘(‘ + Convert(NVARCHAR(8),character_maximum_length) + ‘)’

    WHEN ‘CHAR’ THEN data_type + ‘(‘ + Convert(NVARCHAR(8),character_maximum_length) + ‘)’

    WHEN ‘NUMERIC’ THEN data_type + ‘(‘ + Convert(NVARCHAR(8),numeric_precision) + ‘,’ + Convert(NVARCHAR(8),numeric_scale) + ‘)’

    WHEN ‘DECIMAL’ THEN data_type + ‘(‘ + Convert(NVARCHAR(8),numeric_precision) + ‘,’ + Convert(NVARCHAR(8),numeric_scale) + ‘)’

  END AS data_type,

  is_nullable

FROM   information_schema.columns

WHERE  (table_name = @TABLE)

If you attempt to copy and paste the above code it will fail, so I have uploaded it for you in a nice .sql file located in the big orange box on the right hand side of this blog.  Consider this a gift from me to you!