Post reply

How to search a table in 500 server ?

  • February 22, 2008 at 1:14 am

    #182541

    Hi all,

    I have to find a table which might exists in all databases of 500 servers.

    I don't want to search it manully by login to each server using below method.

    select from sysobjects of each database by applying a cursor for each db in a server

    Can any one suggest the shortest possible way to find this thing both in sql server 2000 and 2005.

    I have a comman login name and password for all the server.

    Regards

    Shashi kant Chauhan

  • February 22, 2008 at 2:20 am

    #780992

    From T-SQL, on SS 2005 you can iterate through your servers in a loop and connect to them and execute a query using OPENROWSET('SQLNCLI'

    OPENROWSET('SQLNCLI','Server=myserver;Trusted_Connection=yes;','SELECT ...')

    However, there are third party tools that allow you to execute a query on multiple servers/databases. Red Gate Software has MultiScript and there is a tool from SQLFarms.

    Also, SQL Server 2008 introduces servergroups, and you can execute a query on a per server basis. This has worse granularity, as it does not allow you to select the database, but if you have the client tools for 2008 February CTP installed, you can use it to connect to your 500 servers at the same time in an easy way, and execute the query with a single click. (note, that the servers you connect to, in the case of all three of the above tools, can be both 2000 and 2005)

    Disclaimer: I work for Red Gate.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • February 22, 2008 at 2:27 am

    #780994

    Hi

    you will have to write a utility that connects to each server

    and within connects to each db to check for the tables existence.

    a doubt..

    You will be executing this utility from one server, so will u be able to connect to all other server from this server ?

    "Keep Trying"

  • February 22, 2008 at 3:30 am

    #781016

    Hi chirag,

    I am using a single server which have the access to all 500 servers.

    If you have any script for doing this either by vb script code or some batch scripts .which i can run through command prompt. or any other alternative of doing this ....

    Please contribute in this thread...

    thanks & regards

    shashi kant chauhan

  • February 22, 2008 at 3:57 am

    #781024

    Hi

    I dont have a script or vb code or anything simply becoz i have never been in a position that you find yourself in.

    What you could do is

    Loop through all the servers and inside this loop

    loop through all the databases and check for the table .

    If you are lucky you might find such a script on the web that you can modify to suit your purpose. You may get the script in this site itself.

    Also check whether any third party software will help you accomplish this and whether you can buy that software.

    Just curious why do you need to do this?

    "Keep Trying"

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply