SQL Server Script to Iterate through servers

Question

I am trying to create a script to obtain data from a multiple databases in a server and then I want it to continue to the next server and do the same. I already created a script to work with one server and all its databases. The script is the same for all databases, tables in all the servers.

My question, is how do I have the script recognize the linked servers that I linked to my current server and continue to obtain data from those servers? Is there a hidden stored procedure or command that someone may know?

Here is my script below, I want to have the script run through 5 servers which I currently have linked to one of my servers:

SET NOCOUNT ON
DECLARE @DBNAME NVARCHAR(MAX)
DECLARE @DBNAME1 NVARCHAR(MAX)
DECLARE @varSQL NVARCHAR(MAX)

DROP TABLE TEMPIMGCOUNTERSERVER3 
CREATE TABLE TEMPIMGCOUNTERSERVER3 
(DBname NVARCHAR(MAX),
Images INT,
FileSize INT,
DBCreation DATETIME)

DECLARE DBNAME CURSOR FAST_FORWARD FOR
select name from sys.databases where [NAME] LIKE 'Z%' AND create_date between '2011-02-06' and '2011-02-12'
ORDER BY [create_date]

OPEN DBNAME

FETCH NEXT FROM DBNAME INTO @DBname
WHILE (@@FETCH_STATUS=0)
BEGIN

    Set @varSQL='INSERT INTO TEMPIMGCOUNTERSERVER3 (DBNAME, IMAGES, FileSize, DBCreation) 
    SELECT ''['+@DBNAME+']'', SUM(PGCOUNT), sum(filesize/1024/1024), sys.databases.CREATE_DATE
        FROM SYS.DATABASES, ['+@DBNAME+'].dbo.tbldoc WHERE created between ''2011-02-06'' and ''2011-02-12''
        and sys.databases.name='''+@DBNAME+''' 
        GROUP BY sys.databases.NAME, sys.databases.CREATE_DATE' 
    EXEC SP_EXECUTESQL @varSQL

    FETCH NEXT FROM DBNAME 
    INTO @DBNAME
    END

    CLOSE DBNAME
    DEALLOCATE DBNAME

    INSERT TEMPIMGCOUNTERSERVER3 (DBNAME, IMAGES, FILESIZE)  
    SELECT @@SERVERNAME + ' ' +'TOTAL IMAGES AND FILE SIZE', SUM(IMAGES), SUM(FILESIZE) FROM TEMPIMGCOUNTERSERVER3 

     SELECT DBNAME, IMAGES, FILESIZE, convert(VARCHAR(10), 
    DBCREATION, 101) AS DBCREATION FROM TEMPIMGCOUNTERSERVER3
    GROUP BY DBNAME, IMAGES, FILESIZE, convert(VARCHAR(10), DBCREATION, 101)
    HAVING SUM(IMAGES) is not null
    ORDER BY DBCREATION

Sem Vanmeenen · Accepted Answer · 2011-02-10 17:36:33Z

1

You can use the sp_linkedservers to get a list of the linked servers. Unfortunately, without seeing your script, I have no idea how easy it is to use this list to execute the script on each linked server.

Off course, in the worst case, you could always build your script as string in combination with the list and use EXEC.

edited Feb 10, 2011 at 17:36

answered Feb 10, 2011 at 17:21

Sem Vanmeenen

2,15112 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2011-02-10 18:20:07Z

Using the linked servers might be one way to do it - but I'd like to recommend you have a look at another tool or two that provide similar functionality - but from the "outside" of your server.

One of those tools would be SQL Deploy which allows you to pick a directory of scripts (or a single script), and then deploy that script / those scripts against any number of servers from a list that you provide / configure.

enter image description here

Somewhat similar is Red-Gate's SQL Multi Script - again, you can pick a number of scripts and have those execute on a number of servers that you define:

enter image description here

Maybe those tools would be worth a look?

Collectives™ on Stack Overflow

SQL Server Script to Iterate through servers

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related