Sql-server – Generating maintenance database from maintenance tables currently in master and msdb

sql serversql-server-2008sql-server-2012

I want to create a maintenance database that i can clean up master and msdb, i am trying to collect all the table info from master and msdb that are not Microsoft tables, bearing in mind that some instances might have different structures due to different requirements. I am getting an error on the code

Msg 102, Level 15, State 1, Line 27
Incorrect syntax near '@ServerName'

is there maybe a simpler way of doing this? 

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

Best Answer

The following code will inspect the master and msdb databases for all linked-servers on the current instance, reporting non-Microsoft-shipped tables:

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;

Your code has two problems;

  1. You cannot use a variable in OPENQUERY like this:

    SELECT * FROM OPENQUERY(@ServerName, '

    That would need to be:

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

    Clearly, that won't work for your case since you're trying to do this dynamically. To make that work, you'd need to do dynamic SQL inside dynamic SQL.

  2. This line contains an embedded reference to the @ServerName variable, but that variable is not visible inside the dynamic T-SQL... That line would need to be:

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