SQL Server 2005: Query table information

-- SQL Server 2005
-- Query table information

DECLARE @object_name SYSNAME
SET @object_name = 'mytable'

SELECT o.type_desc 'object type'
     , s.name 'schema'
     , o.name 'object name'
     , c.name 'col name'
     , t.name type
     , c.max_length 'length'
     , c.precision
     , c.scale
     , CASE
           WHEN c.is_nullable = 0 THEN 'No'
           ELSE 'Yes'
       END nullable
     , CASE
           WHEN p.value IS NULL THEN ''
           ELSE p.value
       END 'description'
     , (
            SELECT CASE WHEN COUNT(*) > 0 THEN 'Yes' ELSE ''END
                    FROM sys.indexes i
                    INNER
                    JOIN sys.index_columns k
                      ON i.object_id = k.object_id
                     AND k.index_id = i.index_id
                     AND k.column_id = c.column_id
                    WHERE i.object_id = o.object_id
                      AND i.type IN (1, 2)
       ) 'indexed'
     , CASE
           WHEN q.value IS NULL THEN ''
           ELSE q.value
       END 'object description'
     , c.column_id 'col'

    FROM sys.objects o
    INNER
    JOIN sys.schemas s
      ON o.schema_id = s.schema_id
    LEFT
    JOIN sys.columns c
      ON o.object_id = c.object_id
    LEFT
    JOIN sys.types t
      ON c.user_type_id = t.user_type_id 
    -- col description
    LEFT
    JOIN sys.extended_properties p
      ON c.object_id  = p.major_id
     AND c.column_id = p.minor_id
    -- object description
    LEFT
    JOIN sys.extended_properties q 
      ON o.object_id = q.major_id
     AND q.minor_id = 0

    WHERE o.name = @object_name
       OR c.name = @object_name

    ORDER BY c.column_id
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s