Stored Procedure Definition Search

Posted on June 29, 2012 by Zach Stagers

I just re-discovered this little script in my “Magic SQL” folder, and thought I’d share it.

Below is a script to search the definition of all stored procedures in a database for a specified value – useful in circumstances when you’re trying to figure out if a particular column is used anywhere in procedure code, or something like that.

USE DatabaseName
 
DECLARE @SearchString VARCHAR(500)
SET @SearchString = 'Search'
 
SELECT DISTINCT
       Name
 
FROM sysobjects SO
     JOIN syscomments SC ON SO.id = SC.id
 
WHERE SO.[type] = 'P'
      AND SO.category = 0
      AND SC.[text] LIKE '%' + @SearchString + '%'

To use the script, change the USE statement to point at the database you’d like to search, and change the value of @SearchString to the value you’d like to search for.

The result set will provide the name of any procedures containing the value defined within @SearchString.

Finding a list of user defined tables with schemas within a SQL Database

Posted on June 1, 2012 by Zach Stagers

A couple of weeks ago I did a blog post on finding a list of user defined tables within a SQL Database. Well, today I’ve improved upon that example to include the schema that the table belongs too as well, plus a little trick to generate you some useful scripts.

USE Database1
 
SELECT S.name + '.' + O.name [SchemaTable]
 
FROM sys.all_objects O JOIN
     sys.schemas S ON S.schema_id = O.schema_id
 
WHERE type = 'U' AND
      is_ms_shipped = 0 AND
      S.schema_id IN (5, 6, 7, 8, 9)
 
ORDER BY O.name

The reason I needed a list of tables and the schemas they belong to in this format was because I needed to add two columns to each of them.

I’m far to busy to open up each of these tables (there were 50 in total) and add the two columns manually, so I used the above SQL, as well as some concatenation to write the query for me:

USE Database1
 
SELECT 'ALTER TABLE ' + S.name + '.' + O.name + ' ADD Child VARCHAR(100) NULL,
       GrandChild VARCHAR(100) NULL' [SQL]
 
FROM sys.all_objects O JOIN
     sys.schemas S ON S.schema_id = O.schema_id
 
WHERE type = 'U' AND
      is_ms_shipped = 0 AND
      S.schema_id IN (5, 6, 7, 8, 9)
 
ORDER BY O.name

I was then able to copy the results into a new query window and execute, saving myself a lot of time.

Finding a list of user defined tables within a SQL Database

Posted on May 8, 2012 by Zach Stagers

Like most companies working with development, we have a production database server and a test database server. The developers do their testing from the test server, and once they’ve finished their development phase and the project is signed off – the database is backed up and restored on the live server.

Occasionally, additional development is required after the site is live (Phase 2, say) – which could mean more database tables to be added.

The above situation happened to me today, and one of the developers asked me to compare the two databases tables, and create any that weren’t already on the production server.

I found a quick way of comparing the two servers, rather than sitting there writing out a list of the two servers tables.

That quick way was the below query:

USE Database1
 
SELECT *
 
FROM sys.all_objects
 
WHERE type = 'U' AND
      is_ms_shipped = 0
 
ORDER BY name

This will find all of the user defined tables associated with the database its run against.

If you remove “AND is_ms_shipped = 0″ from the statement, then you’ll notice that a table named “dtproperties” is listed. This is actually a system table used to store information about database diagrams. It comes back as a user defined table due to the way it’s stored internally. Luckily Microsoft have added the is_ms_shipped flag to the table, so that we can easily filter items like this out.

Scratchbox | Zach Stagers

SQL Server Developer and Administrator

Tag Archives: System Tables

Stored Procedure Definition Search

Finding a list of user defined tables with schemas within a SQL Database

Finding a list of user defined tables within a SQL Database