intellectually constipated

patrick gallucci's technology brain drain

Column Information View for SQL Server

Column Information View for SQL Server

Rate This
  • Comments 4

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
                    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]
 
Comments
Page 1 of 1 (4 items)
Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post