Re: Stored procedure to check the syntax of stored procs, views, ...
From: Andrew (nonsemantic_at_aol.com)
Date: 07/17/04
- Next message: Andrew John: "Re: SQL2000 and Yukon on same computer"
- Previous message: Girish: "sorting problem"
- In reply to: Veerle: "Stored procedure to check the syntax of stored procs, views, ..."
- Messages sorted by: [ date ] [ thread ]
Date: 16 Jul 2004 18:44:56 -0700
Here's what I'd do:
1.) I use a stored procedure called sp_grep. It'll search through the
syscomments table and look for references to your target string. You
can get it here:
http://examples.oreilly.com/wintrnssql/
(d/l the file called "example.zip" and look for sp_grep)
I would use this in a cursor to:
2.) Loop through all the objects in the sysobjects table and do an
sp_grep
on each object's name. This will create a list of all stored procs,
user functions, views, etc. that contain a reference to this object.
Since you're looping through sysobjects, you can filter out any object
types you don't want to include (maybe, for instance, you're not
interested in stored procedures that call the system tables).
3.) Insert this list into a temp table or some other suitable object.
If you're concerned about the table size, then you can use another
table (perhaps a memory table) to get the raw results, then insert
into the temp table where memory table entry is not in the temp table.
4.) Once this sucker has finshed executing (I suspect it'd take some
time to run, i.e. maybe 10 minutes or more), you have a table of all
the objects that are getting called by stored procedures, views, user
functions, etc.
5.) Finally, do something like "select * from sysobjects where name
not in (select distinct name from #tempUsed)". This will give you a
list of objects in your database that aren't being called by stored
procs, user functions, etc. You may want to exclude system objects
just so there's no confusion (system objects have their own xtypes, I
believe, for all the different types of objects).
This is a very rough method and not very fast, but if I had to do a
quick-and-dirty check on my database, that's how I'd do it. I'd be
happy to post example code (I can't post the sp_grep code, though --
but you can download that yourself) if any of this is unclear.
-Andrew-
veerleverbr@hotmail.com (Veerle) wrote in message news:<16c55915.0407142217.7c565f94@posting.google.com>...
> Hi,
>
> It is only possible to create a stored procedure, views, triggers and
> user defined functions if
> (a) the syntax is correct
> (b) the objects (tables, views, ...) used, exist in the db
>
> For example, when a stored procedure is created, it is possible to
> rename one of the tables that is used, but not rename it in the code
> of the stored procedure. This makes the stored procedure invalid: when
> you try to execute it, you get an error.
>
> What I would like to have is a script that finds all the views, stored
> procedures, triggers and functions that use not existing column names,
> not existing tables, not existing views, not existing stored
> procedure, stored procedures with a wrong number of parameters, ...
>
> Finding all the views, stored procedures, triggers and functions
> through a select on sysobjects is quite easy, but how can i check
> them? Trying to execute them all is not an option because (a) there
> are too many of them and (b) this has an impact on the data which we
> do not want. Any idea's?
>
> Veerle
- Next message: Andrew John: "Re: SQL2000 and Yukon on same computer"
- Previous message: Girish: "sorting problem"
- In reply to: Veerle: "Stored procedure to check the syntax of stored procs, views, ..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
