主页 / dba / 问题 / 215385
Accepted
Vorster
Asked: 2018-08-21 04:54:45 +0800 CST

从当前在 master 和 msdb 中的维护表生成维护数据库

  • 772

我想创建一个可以清理 master 和 msdb 的维护数据库，我正在尝试从 master 和 msdb 收集所有不是 Microsoft 表的表信息，请记住，由于不同的要求，某些实例可能具有不同的结构。我收到代码错误

消息 102，级别 15，状态 1，第 27 行“@ServerName”附近的语法不正确

有没有更简单的方法来做到这一点？ 

DECLARE servers_cursor CURSOR
    FOR
    -- select * from environment
    -- select * from DatabaseInfo
        select  distinct LinkedserverName,Environment,ServerUse
        from SQLMonitor.dbo.Environment
        join master..sysservers on srvname COLLATE DATABASE_DEFAULT = LinkedserverName COLLATE DATABASE_DEFAULT
        where ServerUse = 'DEV'
        --and IncludeBackupsStats = 1
        order by LinkedServerName

    OPEN servers_cursor
    DECLARE @Servername varchar(255)
    DECLARE @sql varchar(8000)
    DECLARE @Environment char(4)
    DECLARE @ServerUse varchar(15)

    FETCH NEXT FROM servers_cursor INTO @ServerName,@Environment,@ServerUse
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT @ServerName
        CREATE TABLE [_TempDetails_]
        (DatabaseName sysname
        ,TableName sysname
        ,ActualCommand nvarchar(max))
        INSERT INTO [TempTableScripts].dbo._TempDetails_
        SELECT * FROM OPENQUERY(@ServerName, '
    declare @TableNames table
    (
        DatabaseName sysname
        , TableName sysname
    )
        insert @TableNames
        EXEC sp_msforeachdb @command1 = ''use ?;
        IF ''''?'''' IN (''''master'''',''''msdb'''')
        BEGIN
        SELECT ''''?'''', name from sys.tables T WHERE is_ms_shipped = 0
        END'+'

    select *, ''''SELECT TOP 1 * INTO ['''' + @ServerName + ''''_-_'''' + Databasename +''''_-_'''' + TableName +''''] FROM ['''' + @ServerName+''''].[''''+ Databasename + ''''].dbo.['''' + TableName + '''']'''' 
    from @TableNames
    ')
            --exec (@sql)
      FETCH NEXT FROM servers_cursor INTO @ServerName,@Environment,@ServerUse
      --PRINT @ServerName
    END
    CLOSE servers_cursor
    DEALLOCATE servers_cursor
sql-server sql-server-2012

1 个回答

  1. Best Answer

    以下代码将检查当前实例上所有链接服务器的 master 和 msdb 数据库，报告非 Microsoft 提供的表：

    IF OBJECT_ID(N'tempdb..#obj_list', N'U') IS NOT NULL
DROP TABLE #obj_list;
CREATE TABLE #obj_list
(
    ServerName sysname NOT NULL
    , TableName sysname NOT NULL
);
DECLARE @server sysname;
DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT s.name
FROM sys.servers s
WHERE s.name <> @@SERVERNAME;

OPEN cur;
FETCH NEXT FROM cur INTO @server;
WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = '
SELECT ServerName = ''' + QUOTENAME(@server) + N'''
    , TableName = N''master.'' + s.name + N''.'' + t.name
FROM ' + QUOTENAME(@server) + N'.master.sys.tables t
    INNER JOIN ' + QUOTENAME(@server) + N'.master.sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
UNION ALL
SELECT ServerName = ''' + QUOTENAME(@server) + N'''
    , N''msdb.'' + s.name + N''.'' + t.name
FROM ' + QUOTENAME(@server) + N'.msdb.sys.tables t
    INNER JOIN ' + QUOTENAME(@server) + N'.msdb.sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
ORDER BY TableName;';

    INSERT INTO #obj_list (ServerName, TableName)
    EXEC sys.sp_executesql @cmd;
    FETCH NEXT FROM cur INTO @server;
END
CLOSE cur;
DEALLOCATE cur;

SELECT *
FROM #obj_list ol
ORDER BY ol.ServerName
    , ol.TableName;

    您的代码有两个问题；

    1. 您不能OPENQUERY像这样使用变量：

      SELECT * FROM OPENQUERY(@ServerName, '

      那需要是：

      SELECT * FROM OPENQUERY([Server\Instance], '

      显然，这对您的情况不起作用，因为您正在尝试动态执行此操作。要实现这一点，您需要在动态 SQL 中执行动态 SQL。

    2. 此行包含对变量的嵌入式引用@ServerName，但该变量在动态 T-SQL 中不可见......该行需要是：

      select *, ''''SELECT TOP 1 * INTO [''''' + @ServerName + '''''_-_'''' + Databasename +''''_-_'''' + TableName +''''] FROM [''''' + @ServerName + '''''].[''''+ Databasename + ''''].dbo.['''' + TableName + '''']''''
    • 1

相关问题