Column Information View for SQL Server

Artículo
08/24/2007

Here is a view that contains column information. This is useful if you need to create a DDL statement for a table.

 CREATE VIEW dbo.vColumnInfo

AS

 SELECT

     tbl.name AS [Table_Name],

     SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],

     CAST(ISNULL(cik.index_column_id , 0) AS BIT) AS [InPrimaryKey],

     CAST(ISNULL((

                   SELECT TOP 1

                   FROM

                     sys.foreign_key_columns AS colfk

                   WHERE

                     colfk.parent_column_id = clmns.column_id

                     AND colfk.parent_object_id = clmns.object_id

                 ) , 0) AS BIT) AS [IsForeignKey],

     QUOTENAME(clmns.name) AS [Column_Name],

     clmns.object_id AS object_id,

     clmns.column_id AS column_id,

     clmns.is_computed AS Is_Computed,

     ISNULL(cc.definition , N'') AS computed_column_definition,

     QUOTENAME(usrt.name)

     + CASE usrt.name

         WHEN 'binary'

         THEN CASE clmns.max_length

                WHEN -1 THEN '(max) '

                ELSE ' ('

                     + CAST(clmns.max_length / 2 AS VARCHAR(20))

                     + ') '

END

         WHEN 'char'

         THEN CASE clmns.max_length

                WHEN -1 THEN '(max) '

                ELSE ' ('

                     + CAST(clmns.max_length / 2 AS VARCHAR(20))

                     + ') '

END

         WHEN 'decimal'

         THEN ' (' + CAST(clmns.precision AS VARCHAR(20))

              + ',' + CAST(clmns.max_length AS VARCHAR(20))

              + ') '

         WHEN 'nchar'

         THEN CASE clmns.max_length

                WHEN -1 THEN '(max) '

                ELSE ' ('

                     + CAST(clmns.max_length / 2 AS VARCHAR(20))

                     + ') '

END

         WHEN 'numeric'

         THEN ' (' + CAST(clmns.precision AS VARCHAR(20))

              + ',' + CAST(clmns.max_length AS VARCHAR(20))

              + ') '

         WHEN 'nvarchar'

         THEN CASE clmns.max_length

                WHEN -1 THEN '(max) '

                ELSE ' ('

                     + CAST(clmns.max_length / 2 AS VARCHAR(20))

                     + ') '

END

         WHEN 'varbinary'

         THEN CASE clmns.max_length

                WHEN -1 THEN '(max) '

                ELSE ' ('

                     + CAST(clmns.max_length / 2 AS VARCHAR(20))

                     + ') '

END

         WHEN 'varchar'

         THEN CASE clmns.max_length

                WHEN -1 THEN '(max) '

                ELSE ' ('

                     + CAST(clmns.max_length / 2 AS VARCHAR(20))

                     + ') '

END

         WHEN 'xml'

         THEN CASE WHEN ISNULL(xscclmns.name , N'') = ''

                   THEN ''

                   ELSE ' (CONTENT ' + QUOTENAME(s2clmns.name)

                        + '.' + QUOTENAME(xscclmns.name)

                        + ') '

END

         ELSE ''

       END AS Data_Type,

     clmns.is_identity AS is_identity,

     CASE clmns.is_identity

       WHEN 1

       THEN ' IDENTITY ('

            + CAST(ISNULL(ic.seed_value , 0) AS VARCHAR(10))

            + ','

            + CAST(ISNULL(ic.increment_value , 0) AS VARCHAR(10))

            + ') '

       ELSE ' '

     END AS [Identity],

     clmns.is_nullable AS is_nullable,

     CASE clmns.is_nullable

       WHEN 1 THEN ' NULL '

       ELSE ' NOT NULL '

     END AS [Nullable],

     CASE WHEN cstr.name <> ''

          THEN ' CONSTRAINT ' + QUOTENAME(cstr.name)

               + ' DEFAULT ' + cstr.definition + ','

          ELSE ''

     END AS [Constraint],

     QUOTENAME(clmns.name) + ' '

     + CASE clmns.is_computed

         WHEN 1

         THEN ' AS ' + ISNULL(cc.definition , N'') + ','

         ELSE QUOTENAME(usrt.name)

              + CASE usrt.name

                  WHEN 'binary'

                  THEN CASE clmns.max_length

                         WHEN -1 THEN '(max) '

                         ELSE ' ('

                              + CAST(clmns.max_length / 2 AS VARCHAR(20))

                              + ') '

END

                  WHEN 'char'

                  THEN CASE clmns.max_length

                         WHEN -1 THEN '(max) '

                         ELSE ' ('

                              + CAST(clmns.max_length / 2 AS VARCHAR(20))

                              + ') '

END

                  WHEN 'decimal'

                  THEN ' ('

                       + CAST(clmns.precision AS VARCHAR(20))

                       + ','

                       + CAST(clmns.max_length AS VARCHAR(20))

                       + ') '

                  WHEN 'nchar'

                  THEN CASE clmns.max_length

                         WHEN -1 THEN '(max) '

                         ELSE ' ('

                              + CAST(clmns.max_length / 2 AS VARCHAR(20))

                              + ') '

END

                  WHEN 'numeric'

                  THEN ' ('

                       + CAST(clmns.precision AS VARCHAR(20))

                       + ','

                       + CAST(clmns.max_length AS VARCHAR(20))

                       + ') '

                  WHEN 'nvarchar'

                  THEN CASE clmns.max_length

                         WHEN -1 THEN '(max) '

                         ELSE ' ('

                              + CAST(clmns.max_length / 2 AS VARCHAR(20))

                              + ') '

END

                  WHEN 'varbinary'

                  THEN CASE clmns.max_length

                         WHEN -1 THEN '(max) '

                         ELSE ' ('

                              + CAST(clmns.max_length / 2 AS VARCHAR(20))

                              + ') '

END

                  WHEN 'varchar'

                  THEN CASE clmns.max_length

                         WHEN -1 THEN '(max) '

                         ELSE ' ('

                              + CAST(clmns.max_length / 2 AS VARCHAR(20))

                              + ') '

END

                  WHEN 'xml'

                  THEN CASE WHEN ISNULL(xscclmns.name , N'') = ''

                            THEN ''

                            ELSE ' (CONTENT '

                                 + QUOTENAME(s2clmns.name)

                                 + '.'

                                 + QUOTENAME(xscclmns.name)

                                 + ') '

END

                  ELSE ''

END

              + CASE clmns.is_identity

                  WHEN 1

                  THEN ' IDENTITY ('

                       + CAST(ISNULL(ic.seed_value , 0) AS VARCHAR(10))

                       + ','

                       + CAST(ISNULL(ic.increment_value , 0) AS VARCHAR(10))

                       + ') '

                  ELSE ' '

                END + CASE clmns.is_nullable

                        WHEN 1 THEN ' NULL '

                        ELSE ' NOT NULL '

END

              + CASE WHEN cstr.name <> ''

                     THEN ' CONSTRAINT '

                          + QUOTENAME(cstr.name)

                          + ' DEFAULT ' + cstr.definition

                          + ','

                     ELSE ','

END

       END AS [Column_Text]

 FROM

     sys.tables AS tbl

 INNER JOIN sys.all_columns AS clmns

 ON  clmns.object_id = tbl.object_id

 LEFT OUTER JOIN sys.indexes AS ik

 ON  ik.object_id = clmns.object_id

     AND 1 = ik.is_primary_key

 LEFT OUTER JOIN sys.index_columns AS cik

 ON  cik.index_id = ik.index_id

     AND cik.column_id = clmns.column_id

     AND cik.object_id = clmns.object_id

     AND 0 = cik.is_included_column

 LEFT OUTER JOIN sys.computed_columns AS cc

 ON  cc.object_id = clmns.object_id

     AND cc.column_id = clmns.column_id

 LEFT OUTER JOIN sys.types AS usrt

 ON  usrt.user_type_id = clmns.user_type_id

 LEFT OUTER JOIN sys.types AS baset

 ON  baset.user_type_id = clmns.system_type_id

     AND baset.user_type_id = baset.system_type_id

 LEFT OUTER JOIN sys.schemas AS sclmns

 ON  sclmns.schema_id = usrt.schema_id

 LEFT OUTER JOIN sys.identity_columns AS ic

 ON  ic.object_id = clmns.object_id

     AND ic.column_id = clmns.column_id

 LEFT OUTER JOIN sys.objects AS d

 ON  d.object_id = clmns.default_object_id

 LEFT OUTER JOIN sys.objects AS r

 ON  r.object_id = clmns.rule_object_id

 LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns

 ON  xscclmns.xml_collection_id = clmns.xml_collection_id

 LEFT OUTER JOIN sys.schemas AS s2clmns

 ON  s2clmns.schema_id = xscclmns.schema_id

 LEFT OUTER JOIN sys.default_constraints AS cstr

 ON  cstr.object_id = clmns.default_object_id

The select statement:

SELECT Column_Text FROM dbo.vColumnInfo
ORDER BY
object_id,
column_id

Returns:

[Table_DataType_ID] [bigint] IDENTITY (1,1) NOT NULL ,
[FK_Table_FK_01_ID] [bigint] NOT NULL ,
[col_guid] [uniqueidentifier] NULL CONSTRAINT [Guid_Default] DEFAULT (newsequentialid()),
[col_bigint] [bigint] NOT NULL CONSTRAINT [DF_Table_DataType_col_bigint] DEFAULT ((0)),
[col_bit] [bit] NOT NULL CONSTRAINT [DF_Table_DataType_col_bit] DEFAULT ((1)),
[col_binary] [binary] (0)   NULL CONSTRAINT [DF_Table_DataType_col_binary] DEFAULT ((2)),
[col_char] AS (left([col_nchar],N'3')),
[col_datetime] AS (getdate()),
[col_decimal] [decimal] (18,9)   NULL CONSTRAINT [DF_Table_DataType_col_decimal] DEFAULT ((5)),
[col_float] [float] NULL CONSTRAINT [DF_Table_DataType_col_float] DEFAULT ((6)),
[col_image] [image] NULL ,
[col_int] [int] NOT NULL CONSTRAINT [DF_Table_DataType_col_int] DEFAULT ((8)),
[col_money] [money] NOT NULL CONSTRAINT [DF_Table_DataType_col_money] DEFAULT ((9)),
[col_nchar] [nchar] (100)   NULL CONSTRAINT [DF_Table_DataType_col_nchar] DEFAULT (N'10'),
[col_ntext] [ntext] NOT NULL CONSTRAINT [DF_Table_DataType_col_ntext] DEFAULT (N'11'),
[col_numeric] [numeric] (18,9)   NOT NULL CONSTRAINT [DF_Table_DataType_col_numeric] DEFAULT ((12)),
[col_nvarchar] [nvarchar] (256)   NOT NULL CONSTRAINT [DF_Table_DataType_col_nvarchar] DEFAULT (N'13'),
[col_real] [real] NOT NULL CONSTRAINT [DF_Table_DataType_col_real] DEFAULT (N'14'),
[col_smalldatetime] [smalldatetime] NOT NULL CONSTRAINT [DF_Table_DataType_col_smalldatetime] DEFAULT (N'15'),
[col_smallint] [smallint] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallint] DEFAULT ((16)),
[col_smallmoney] [smallmoney] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallmoney] DEFAULT ((17)),
[col_sql_variant] [sql_variant] NOT NULL CONSTRAINT [DF_Table_DataType_col_sql_variant] DEFAULT ((18)),
[col_sysname] [sysname] NULL ,
[col_text] [text] NOT NULL CONSTRAINT [DF_Table_DataType_col_text] DEFAULT ('20'),
[col_timestamp] [timestamp] NULL ,
[col_tinyint] [tinyint] NOT NULL CONSTRAINT [DF_Table_DataType_col_tinyint] DEFAULT ((22)),
[col_uniqueidentifier] [uniqueidentifier] NULL ,
[col_varbinary] [varbinary] (0)   NOT NULL CONSTRAINT [DF_Table_DataType_col_varbinary] DEFAULT ((24)),
[col_varchar] [varchar] (512)   NOT NULL CONSTRAINT [DF_Table_DataType_col_varchar] DEFAULT ('25'),
[col_xml] [xml] (CONTENT [dbo].[PartitionDemoSchemaCollection])   NULL ,

For the table definition:

 CREATE TABLE [dbo].[Table_DataType](

     [Table_DataType_ID] [bigint] IDENTITY(1,1) NOT NULL,

     [FK_Table_FK_01_ID] [bigint] NOT NULL,

     [col_guid] [uniqueidentifier] ROWGUIDCOL  NULL CONSTRAINT [Guid_Default]  DEFAULT (newsequentialid()),

     [col_bigint] [bigint] NOT NULL CONSTRAINT [DF_Table_DataType_col_bigint]  DEFAULT ((0)),

     [col_bit] [bit] NOT NULL CONSTRAINT [DF_Table_DataType_col_bit]  DEFAULT ((1)),

     [col_binary] [binary](1) NULL CONSTRAINT [DF_Table_DataType_col_binary]  DEFAULT ((2)),

     [col_char]  AS (left([col_nchar],N'3')),

     [col_datetime]  AS (getdate()),

     [col_decimal] [decimal](18, 9) NULL CONSTRAINT [DF_Table_DataType_col_decimal]  DEFAULT ((5)),

     [col_float] [float] NULL CONSTRAINT [DF_Table_DataType_col_float]  DEFAULT ((6)),

     [col_image] [image] NULL,

     [col_int] [int] NOT NULL CONSTRAINT [DF_Table_DataType_col_int]  DEFAULT ((8)),

     [col_money] [money] NOT NULL CONSTRAINT [DF_Table_DataType_col_money]  DEFAULT ((9)),

     [col_nchar] [nchar](100) NULL CONSTRAINT [DF_Table_DataType_col_nchar]  DEFAULT (N'10'),

     [col_ntext] [ntext] NOT NULL CONSTRAINT [DF_Table_DataType_col_ntext]  DEFAULT (N'11'),

     [col_numeric] [numeric](18, 9) NOT NULL CONSTRAINT [DF_Table_DataType_col_numeric]  DEFAULT ((12)),

     [col_nvarchar] [nvarchar](256) NOT NULL CONSTRAINT [DF_Table_DataType_col_nvarchar]  DEFAULT (N'13'),

     [col_real] [real] NOT NULL CONSTRAINT [DF_Table_DataType_col_real]  DEFAULT (N'14'),

     [col_smalldatetime] [smalldatetime] NOT NULL CONSTRAINT [DF_Table_DataType_col_smalldatetime]  DEFAULT (N'15'),

     [col_smallint] [smallint] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallint]  DEFAULT ((16)),

     [col_smallmoney] [smallmoney] NOT NULL CONSTRAINT [DF_Table_DataType_col_smallmoney]  DEFAULT ((17)),

     [col_sql_variant] [sql_variant] NOT NULL CONSTRAINT [DF_Table_DataType_col_sql_variant]  DEFAULT ((18)),

     [col_sysname] [sysname] NULL,

     [col_text] [text] NOT NULL CONSTRAINT [DF_Table_DataType_col_text]  DEFAULT ('20'),

     [col_timestamp] [timestamp] NULL,

     [col_tinyint] [tinyint] NOT NULL CONSTRAINT [DF_Table_DataType_col_tinyint]  DEFAULT ((22)),

     [col_uniqueidentifier] [uniqueidentifier] NULL,

     [col_varbinary] [varbinary](1) NOT NULL CONSTRAINT [DF_Table_DataType_col_varbinary]  DEFAULT ((24)),

     [col_varchar] [varchar](1024) NOT NULL CONSTRAINT [DF_Table_DataType_col_varchar]  DEFAULT ('25'),

     [col_xml] [xml](CONTENT [dbo].[PartitionDemoSchemaCollection]) NULL,

  CONSTRAINT [PK_Table_DataType] PRIMARY KEY CLUSTERED

     [Table_DataType_ID] ASC,

     [col_bigint] ASC

 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Technorati Tags: SQL, SQL Server, DDL, CREATE TABLE, VIEW, Information_schema

Comments

Anonymous
August 24, 2007
PingBack from http://msdnrss.thecoderblogs.com/2007/08/24/column-information-view-for-sql-server/
Anonymous
August 24, 2007
PingBack from http://msdnrss.thecoderblogs.com/2007/08/24/column-information-view-for-sql-server/
Anonymous
August 24, 2007
Here is a view that contains column information. This is useful if you need to create a DDL statement
Anonymous
August 25, 2007
Can't we use the facility available in Management studio itself? why re-invent the wheel? You can, but if you need to create create a table in tsql code that is a mirror of one table, you need some facility to do this. That is actually why I created this. I will be posting on this exact topic this week, using this view. Thanks for the Q.

Compartir a través de

Column Information View for SQL Server

Comments

Recursos adicionales