How do I introspect on a SQL Server?

Question

I have a server with a vendor application which is heavily database-reliant. I need to make some minor changes to the data in a few tables in the database in an automated fashion. Just INSERTs and UPDATEs, nothing fancy. Vendors being vendors, I can never be quite sure when they change the schema of a database during upgrade.

To that end, how do I ask the SQL server, in some scriptable fashion, "Hey, does this table still exist? Yeah, cool, okay, but does it have this column? What's the data type and size on that? Is it nullable? Could you give me a list of tables? In this table, could you give me a list of columns? Any primary keys there?" I do not need to do this for the whole schema, only part of it, just a quick check of the database before I launch into things.

We have Microsoft SQL Server 2005 on it currently, but it might easily move to Microsoft SQL Server 2008. I am probably not using the correct terminology when searching. I do know that ORM is not only too much overhead for this sort of thing, but also that I have no chance of pitching it to my coworkers.

Answer 1

Check out the Information_Schema views.

Answer 2

Run a query like the listed below, from it you can see:

• schema name
• table/view name
• table type (like: SYSTEM_TABLE, VIEW, SQL_TABLE_VALUED_FUNCTION, USER_TABLE, SQL_INLINE_TABLE_VALUED_FUNCTION, INTERNAL_TABLE)
• column name
• column data type (including length, precision, etc)
• Nullability
• this column's position in the Primary Key
• complete Primary Key, all PK columns concatenated together, if this column is part of the PK
• identity (seed, increment, and current value)
• check constraint definition
• computed column definition

Needs SQL Server 2005+ to run:

--optional, remove comments on WHERE to use these
--DECLARE @SchemaNameSearch   sysname
--       ,@TableNameSearch    sysname
--       ,@ColumnNameSearch   sysname
--SELECT @SchemaNameSearch  ='YourSchemaName'
--      ,@TableNameSearch   ='YourTableName'
--      ,@ColumnNameSearch  ='YourColumnName'

SELECT
    sh.name+'.'+o.name AS ObjectName
        ,o.type_desc AS ObjectType
        ,s.name as ColumnName
        ,CASE
             WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
             WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
             WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
             ELSE t.name
         END AS DataType

        ,CASE
             WHEN s.is_nullable=1 THEN 'NULL'
            ELSE 'NOT NULL'
        END AS Nullable
        ,xc.key_ordinal AS PK_Position
        ,CASE
             WHEN xc.key_ordinal IS NOT NULL THEN All_PKs.PrimaryKey
             ELSE NULL
         END AS PK
        ,CASE
             WHEN ic.column_id IS NULL THEN ''
             ELSE ' identity('+ISNULL(CONVERT(varchar(10),ic.seed_value),'')+','+ISNULL(CONVERT(varchar(10),ic.increment_value),'')+')='+ISNULL(CONVERT(varchar(10),ic.last_value),'null')
         END
        +CASE
             WHEN sc.column_id IS NULL THEN ''
             ELSE ' computed('+ISNULL(sc.definition,'')+')'
         END
        +CASE
             WHEN cc.object_id IS NULL THEN ''
             ELSE ' check('+ISNULL(cc.definition,'')+')'
         END
            AS MiscInfo
    FROM sys.objects                           o
        INNER JOIN sys.schemas                sh on o.schema_id=sh.schema_id
        INNER JOIN sys.columns                 s ON o.object_id=s.object_id
        INNER JOIN sys.types                   t ON s.system_type_id=t.system_type_id and t.is_user_defined=0
        LEFT OUTER JOIN sys.identity_columns  ic ON s.object_id=ic.object_id AND s.column_id=ic.column_id
        LEFT OUTER JOIN sys.computed_columns  sc ON s.object_id=sc.object_id AND s.column_id=sc.column_id
        LEFT OUTER JOIN sys.check_constraints cc ON s.object_id=cc.parent_object_id AND s.column_id=cc.parent_column_id
        LEFT OUTER JOIN sys.indexes            x ON o.object_id=x.object_id AND x.is_primary_key=1
        LEFT OUTER JOIN sys.index_columns     xc ON o.object_id=xc.object_id AND x.index_id=xc.index_id AND s.column_id=xc.column_id
        LEFT OUTER JOIN (SELECT --build the concatenated PK here
                             oo.object_id
                                  ,STUFF(
                                             (
                                              SELECT 
                                                   ', '+s.Name
                                                  FROM sys.objects                           o
                                                      LEFT OUTER JOIN sys.indexes            x ON o.object_id=x.object_id AND x.is_primary_key=1
                                                      LEFT OUTER JOIN sys.index_columns     xc ON o.object_id=xc.object_id AND x.index_id=xc.index_id 
                                                      LEFT OUTER JOIN sys.columns            s ON o.object_id=s.object_id AND s.column_id=xc.column_id
                                                 WHERE oo.object_id=o.object_id AND xc.column_id IS NOT NULL
                                                 ORDER BY o.object_ID,xc.key_ordinal
                                                 FOR XML PATH('') 
                                             )
                                            ,1,2, ''
                                        ) AS PrimaryKey
                               FROM sys.objects  oo
                               --
                               --REMOVE comments to filter the query
                               --WHERE oo.Name=@TableNameSearch 
                               --
                        )All_PKs ON o.object_id=All_PKs.object_id
    --
    --REMOVE comments to filter the query
    --WHERE sh.name =@SchemaNameSearch
    --    AND o.Name=@TableNameSearch 
    --    AND s.name=@ColumnNameSearch
    --
    ORDER BY sh.name+'.'+o.name,s.column_id

you can remove the comment on the WHERE to filter by schema/table/column.

You could also just create a database trigger to alert you of changes:

create this log table first:

CREATE TABLE YourLogTable (EventID int not null identity(1,1), EventDateTime datetime null, EventDescription  varchar(MAX) null)

USE [TheDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [YourDatabaseTrigger]
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS --DDL_TABLE_EVENTS    --DDL_EVENTS
AS

DECLARE @EventData      xml
DECLARE @Message        varchar(1000)
SET @EventData=EVENTDATA()

INSERT INTO YourLogTable 
    (EventDateTime,EventDescription) 
    VALUES (GETDATE(),--SUSER_NAME()
                     --+'; '[email protected]('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)')
                     --+'; '[email protected]('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)')
                     --+'; '[email protected]('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(8000)')
                     CONVERT(varchar(max),@EventData)
           )
RETURN

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [YourDatabaseTrigger] ON DATABASE

that will let you see every change made to the database.