如何在 SQL Server 上进行内省？

Question

我有一台带有供应商应用程序的服务器，该应用程序严重依赖数据库。我需要以自动方式对数据库中几个表中的数据进行一些细微的更改。只是插入和更新，没什么花哨的。供应商就是供应商，我永远无法确定他们在升级期间何时更改数据库的架构。

为此，我如何以某种可编写脚本的方式询问 SQL Server，“嘿，这个表还存在吗？是的，很酷，好的，但是它有这个列吗？它的数据类型和大小是什么？是吗？”可以为空吗？您能给我一个表列表吗？在这个表中，您能给我一个列列表吗？”我不需要对整个模式执行此操作，只需对其中的一部分执行此操作，只需在开始操作之前快速检查数据库即可。

目前我们使用的是 Microsoft SQL Server 2005，但它可能很容易迁移到 Microsoft SQL Server 2008。我在搜索时可能没有使用正确的术语。我确实知道 ORM 不仅开销太大对于这类事情，但我也没有机会向我的同事推销它。

Answer 1

查看Information_Schema 视图。

Answer 2

运行如下所示的查询，从中您可以看到：

• 模式名称
• 表/视图名称
• 表类型（例如：SYSTEM_TABLE、VIEW、SQL_TABLE_VALUED_FUNCTION、USER_TABLE、SQL_INLINE_TABLE_VALUED_FUNCTION、INTERNAL_TABLE）
• 列名称
• 列数据类型（包括长度、精度等）
• 可空性
• 此列在主键中的位置
• 完整主键，所有 PK 列连接在一起，如果此列是 PK
• 标识（种子、增量和当前值）
• 的一部分，则检查约束定义
• 计算列定义

需要 SQL Server 2005+ 才能运行：

--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

您可以删除 WHERE 上的注释以按架构/表/列进行过滤。

您还可以创建一个数据库触发器来提醒您发生更改：

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

这将让您看到对数据库所做的每个更改。

Answer 3

要通过 SQL 执行此操作，请使用 INFORMATION_SCHEMA 视图。

要通过代码完成此操作，请查看 SQL Server 管理对象 (SMO)：

http: //msdn.microsoft.com/en-us/library/ms162169.aspx