Databases
StackOverflow2013
Postsdbo
POWish to observe stored procedures that exist on multiple database and across multiple servers
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWish to observe stored procedures that exist on multiple database and across multiple servers
    primarykey
    data
    text
    <p>Having an issue on MS SQL 2005 Enterprise multiple servers where I want to get a collection of meta data across multiple servers and multiple databases. I saw on Stack Overflow a good example on using the magical sp_MSforeachdb that I altered a little bit below. Basically a MS stored procedure is being ran dynamically and it's looking for anytime a database (?) is like a name like 'Case(fourspaces)'. This is great and it gives me what I want but for only a single server. I want to do this for more, is it possible SQL gurus?</p> <p>Example thus far:</p> <pre><code>SET NOCOUNT ON DECLARE @AllTables table (CompleteTableName varchar(256)) INSERT INTO @AllTables (CompleteTableName) EXEC sp_msforeachdb 'select distinct @@SERVERNAME+''.''+ ''?'' + ''.'' + p.name from [?].sys.procedures p (nolock) where ''?'' like ''Case____''' SELECT * FROM @AllTables ORDER BY 1 </code></pre> <p>Is there a way though to do this in SQL, Linq, or ADO.NET to perform this clever built in stored procedure that inserts into a table variable to do this multiple times across servers BUT...... Put that in one set. As far as I know you CANNOT switch servers in a single session in SQL Management Studio but I would love to be proved wrong on that one.</p> <p>EG: I have a production environment with 8 Servers, each of those servers has many databases. I could run this multiple times but I was hoping that if the servers were linked already I could do this from the sys views somehow. However I am on an environment using SQL 2005 and got MS's download for the sys views and it looks like the sys.servers is on an island unto itself where the SERVERID does not seem to join to anything else.</p> <p>I would be willing to use an ADO.NET reader or LINQ in a C# environment and possibly call the above TSQL code multiple times but ...... Is there a more efficient way to get the info directly in TSQL IF the servers are LINKED SERVERS? Just curious.</p> <p>The overall purpose of this operation is for deployment purposes to see how many procedures exist across all servers and databases. Now we do have SQL compare from Redgate but I am unaware if it can script procs that don't exist to exist the same as set A. Even if it could I would like to try to make something on my own if feasible.</p> <p>Any help is much appreciated and if you need further clarification please ask.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USdjangojazz
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWish to observe stored procedures that exist on multiple database and across multiple servers
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf I were to rephrase your question: Given a list of servers, you would like to be able to run a query against all(?) the databases on those looking to see how many stored procedures they have which meet a certain naming pattern (Case____)?
      singulars
      1. POWish to observe stored procedures that exist on multiple database and across multiple servers
      1. USbillinkc
    2. COClose. I want to run a query against all databases that match a pattern on the database for (Case____) for ALL procedures or a user prompted search. Ultimately a user would do an input like
      singulars
      1. POWish to observe stored procedures that exist on multiple database and across multiple servers
      1. USdjangojazz
    3. COexec spgetdeploystats @PROCEDURENAME
      singulars
      1. POWish to observe stored procedures that exist on multiple database and across multiple servers
      1. USdjangojazz
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload