代码之家  ›  专栏  ›  技术社区  ›  MetaHyperBolic

如何在SQL服务器上进行自省?

  •  5
  • MetaHyperBolic  · 技术社区  · 16 年前

    我有一台服务器,它有一个非常依赖数据库的供应商应用程序。我需要以自动化的方式对数据库中几个表中的数据进行一些细微的更改。只是插入和更新,没有什么花哨的。作为供应商,我永远无法确定他们在升级过程中何时更改数据库的模式。

    为此,我如何以某种可脚本化的方式询问SQL Server,“嘿,这个表还存在吗?是的,很酷,好吧,但是有这个专栏吗?上面的数据类型和大小是什么?它可以为空吗?你能给我一张桌子的清单吗?在这张桌子上,你能给我列一张单子吗?有主键吗?”对于整个模式,我不需要这样做,只需要对其中的一部分进行检查,只需在我启动之前快速检查数据库即可。

    我们目前有Microsoft SQL Server 2005,但它可能很容易移动到Microsoft SQL Server 2008。 我在搜索时可能没有使用正确的术语。 我知道ORM不仅对这类事情来说开销太大,而且我没有机会把它交给我的同事。

    3 回复  |  直到 16 年前
        1
  •  5
  •   KM.    16 年前

    运行如下所示的查询,从中可以看到:

    • 模式名
    • 表/视图名称
    • 表类型(如:系统表、视图、SQL表值函数、用户表、SQL内联表值函数、内部表)
    • 列名
    • 列数据类型(包括长度、精度等)
    • 空属性
    • 此列在主键中的位置
    • 完成主键,如果此列是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
    

    您可以删除关于按架构/表/列筛选位置的注释。

    您还可以创建一个数据库触发器来提醒您所做的更改:

    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()
                         --+'; '+@EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)')
                         --+'; '+@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)')
                         --+'; '+@EventData.value('(/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
    

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

        2
  •  8
  •   Austin Salonen gmlacrosse    16 年前
        3
  •  1
  •   Michael Maddox    16 年前

    要通过SQL执行此操作,请使用Information_Schema视图。

    要通过代码来实现,请查看SQL Server管理对象(SMO):

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

    推荐文章