question

Slick84 avatar image
Slick84 asked

Search SQL "Server" for objects

I wanted to search for a stored procedure within our server and did not know the exact database it was in. I tried a few things unsuccessfully but finally decided to google it. I came across this article on SQLServerCentral.com : http://www.sqlservercentral.com/articles/Advanced+Querying/howtofindsqlserverobjects/1446/ which seemed too complicated so I searched more and found a free application called SQLDBSearch which works wonders.

However, I'd much prefer a T-SQL query to search within my server through all databases for stored procedures or any other database objects. Any other idea's? Solutions, etc?

Thanks for all the help in advance.

t-sqlsearchengine
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

sp_msforeachdb is definitely the way to go when combined with the information_schema views.

If the base article is too complicated and you are searching for a procedure by name for instance, you can use something simple like:

sp_msforeachdb ' 
select 
    ''?'', *
from 
    [?].information_schema.routines
where 
    routine_name = ''SillyProc'' 
'
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Aaron Alton avatar image
Aaron Alton answered

Are you trying to search by name, or are you trying to search the content of the table/body of the stored procedure?

For a simple "by name" search, it doesn't get much easier than the sp_msforeachdb solution that you posted from SQLServerCentral. If you set aside 20 minutes to properly read it, you'll find that it's a great, and simple solution.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

Object references can be found in either the sysobjects table (2000) or sys.objects view (2005+). However, as I'm sure you are aware, these exist separately per database. I was going to suggest using the undocumented sp_msforeachdb stored procedure, but fortunately I checked the article you referenced first.

What about it seems too complicated? Have you tried that method?

SQL Server divides objects into databases for security and other reasons. It is not designed to be easy to reference system views across multiple databases.

If, however, you already know the names of the databases you wish to search (and it's a manageable list), you could try this:

DECLARE @ProcName sysname; SET @ProcName = 'sp_creatediagram'

SELECT 'db1' AS DatabaseName, p.name AS ProcName
FROM db1.sys.procedures p
WHERE p.name = @ProcName
UNION ALL
SELECT 'db2' AS DatabaseName, p.name AS ProcName
FROM db2.sys.procedures p
WHERE p.name = @ProcName

It's not nearly as elegant as the general solution in the article, but it will work for small sets of databases. I think it's about as simple as you're going to get. Just replace the database names in the SELECT and FROM clauses and union as many queries as you have databases to search.

If you want to search for any object, use this instead:

DECLARE @ProcName sysname; SET @ProcName = 'sp_creatediagram'

SELECT 'db1' AS DatabaseName, o.name AS ProcName, o.type_desc
FROM db1.sys.objects o
WHERE o.name = @ProcName
UNION ALL
SELECT 'db2' AS DatabaseName, o.name AS ProcName, o.type_desc
FROM db2.sys.objects o
WHERE o.name = @ProcName
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Laerte Junior avatar image
Laerte Junior answered

You said TSQL, but I believe that Powershell can help a lot too!

foreach ( $DatabaseName  in Get-ChildItem SQLSERVER:\SQL\$env:computername\default\databases ) {
    set-location  ("SQLSERVER:\SQL\$env:computername\DEFAULT\databases\" + $DatabaseName.name + "\storedprocedures")
    dir | where {$_.name -match "YOURPROCEDURE"} | select $databasename.Name,schema,owner,name,CreateDate,DateLastModified
}

change DEFAULT for your instance if was named

You have the return list NameDatabase,Schema,Owner,Name (stored procedure), Create date and Date Last Modified. We can a lot of other informations, but I think these are the most important.

This code only works for on server/instance, but f you want to multiple servers/instances, its easy to do in PS too !!!! the code will be uniform for 1 or 10000 servers

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dvroman avatar image
dvroman answered

We use a simple script in-house to find objects with a given string:

DECLARE @Txt VARCHAR(50)
SELECT OBJECT_NAME(id) ObjectName,
        CASE WHEN OBJECTPROPERTY(id, 'IsReplProc') = 1       THEN 'Replication Stored Procedure'
             WHEN OBJECTPROPERTY(id, 'IsExtendedProc') = 1   THEN 'Extended Stored Procedure'
             WHEN OBJECTPROPERTY(id, 'IsProcedure') = 1      THEN 'Stored Procedure'
             WHEN OBJECTPROPERTY(id, 'IsTrigger') = 1        THEN 'Trigger'
             WHEN OBJECTPROPERTY(id, 'IsTableFunction') = 1  THEN 'Table-Valued Function'
             WHEN OBJECTPROPERTY(id, 'IsScalarFunction') = 1 THEN 'Scalar-Valued Function'
             WHEN OBJECTPROPERTY(id, 'IsInlineFunction') = 1 THEN 'Inline function'
        END AS ObjectType
FROM syscomments
WHERE ([TEXT] LIKE @Txt
      )
  AND (OBJECTPROPERTY(id, 'IsProcedure') = 1
    OR OBJECTPROPERTY(id, 'IsReplProc') = 1
    OR OBJECTPROPERTY(id, 'IsExtendedProc') = 1
    OR OBJECTPROPERTY(id, 'IsTrigger') = 1
    OR OBJECTPROPERTY(id, 'IsTableFunction') = 1
    OR OBJECTPROPERTY(id, 'IsScalarFunction') = 1
    OR OBJECTPROPERTY(id, 'IsInlineFunction') = 1)
GROUP BY OBJECT_NAME(id), syscomments.id
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.