How to force a recompile on the stored procs?

I want to recompile all the stored procs in the database. I've tried
sp_recompile on all of them, but it simply marks them to be recompiled.
ThanksThey don't actually get recompiled until the next time they get called.
That is how it always works.
--
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks|||DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks

How to force a recompile on the stored procs?

I want to recompile all the stored procs in the database. I've tried
sp_recompile on all of them, but it simply marks them to be recompiled.
Thanks
They don't actually get recompiled until the next time they get called.
That is how it always works.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks
|||DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks

How to force a recompile on the stored procs?

I want to recompile all the stored procs in the database. I've tried
sp_recompile on all of them, but it simply marks them to be recompiled.
ThanksThey don't actually get recompiled until the next time they get called.
That is how it always works.
Andrew J. Kelly SQL MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks|||DBCC FREEPROCCACHE clears the procedure cache and causes ad hoc queries to
be recompiled
if you want a stored procedure to be compiled you will need to use the WITH
RECOMPILE option
if you want to clear the data cache you will need to use DBCC
DROPCLEANBUFFERS
DBCC FLUSHPROCINDB: Used to clear out the stored procedure cache for a
specific database on a SQL Server, not the entire SQL Server. The database
ID number to be affected must be entered as part of the command.
"Frank Rizzo" <none@.none.com> wrote in message
news:e9d8DSUIGHA.3728@.tk2msftngp13.phx.gbl...
>I want to recompile all the stored procs in the database. I've tried
>sp_recompile on all of them, but it simply marks them to be recompiled.
> Thanks

How to find which stored procs and UDFs reference a column

How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.

Thanks.

Which version of sql server are you using ?|||SQL Server 2000.|||You can try the sql server specific system table "sysdepends". Look it up in Books Online. I am not aware of an INFORMATION SCHEMA view that will help you in determining such references.|||

The sysdepends table seems helpful, but it only gives the table, not the columns. Here's the query I used:

SELECT sp.name as StoredProc, dep.name AS DependentObject
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id AND sp.xtype = 'P'
INNER JOIN sysobjects dep ON dep.id = sd.depid

Any further clues to linking the columns? (I suppose I could query for occurrences of the column name within the stored procedures depending on the column's table, but that's an approximation, since column names in the procedure text could be from a different table.)

|||

The depnumber column in sysdepends should give you the column id of the table that the procedure/function references. You can modify your query above to also join the depnumber column in sysdepends with the id column in syscolumns to get the name of the column.

Let me know if that works for you

|||

Seems I'm close, but the following query doesn't always yield complete results:

SELECT sp.name as StoredProc
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE tbl.name = 'MyTable' AND col.name = 'MyColumn'
ORDER BY sp.name

I'm trying to verify it as follows:

SELECT sp.name as StoredProc, tbl.name AS [Table], col.name AS [Column]
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE sp.name = 'MyProc'
ORDER BY tbl.name, col.name

Thank you for your consideration.

|||

If you are using dynamic sql to create the procedures/functions then sql server may not be able to track the references. Also, if you are using deferred name resolution [i.e create the procedure first and then create the table that is being referenced by the procedure] then sql server will not be able to track the references.

By any chance, is this the case?

|||

There are many cases under which the dependency tracking in SQL Server will not work. Here are the common cases:

1. Creation of dependent stored procedures in out-of-order fashion for example

2. Use of temporary tables or table variables in SELECT/DML statements will defer compilation of the statement so there will be no dependency information saved

3. Use of dynamic SQL

4. In case of permanent tables, if the object doesn't exist then deferred name resolution / compilation will kick-in at run-time and there will be no dependency information for this case also

Please take a look at the blog entry below for some queries on how to do this in SQL Server 2005 assuming that the dependency information is present.

http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx

So given the various restrictions it is probably unlikely that you have dependency information for most objects other than references, schema bound objects etc. You will have to mostly maintain this information manually or use your source code control system to scan your scripts assuming you use some keywords mechanism to tag scripts for example.

|||

I take it back -- after carefully comparing results, it looks like it's working great. Thanks so much for all your help!!

SELECT sp.name AS StoredProc
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE tbl.name = 'MyTable' AND col.name = 'MyColumn'
ORDER BY sp.name

Confirming/cross-checking query:

SELECT obj.[name], cmt.[text]
FROM syscomments cmt
INNER JOIN sysobjects obj ON obj.id = cmt.id
WHERE text like '%MyTable%'
AND [text] LIKE '%MyColumn%'

Reverse query:

SELECT sp.name as StoredProc, tbl.name AS [Table], col.name AS [Column]
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE sp.name = 'MyProc'
ORDER BY tbl.name, col.name

|||I've discovered that the sysdepends table is not reliable/complete unless the stored procedure is created (or recreated) after the table(s) that it references. [Or at least, I can say that querying for columns referenced by a proc may not show up any, but then if the proc is dropped and recreated, the same query can now show its column dependencies.]|||

As Umachandar pointed out, there are instances where sysdepends will not work correctly.

|||Recreating SPs or altering SPs are costly operations since they will block access to the SP metadata. So you have to probably schedule this during a maintenance window. And if you don't have a mechanism to know which ones to alter then you will have to do this for all the SPs and this can take considerable time depending on the number of SPs/UDFs in the database. So there are several issues if you rely completely on the server dependency information.

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
--
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.giganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
Karen
Quickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegro ups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.giganews.com ...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael
|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.
|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegro ups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.gi
ganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

How to find what views a table is used

I want to find all views using a particular table name.
Also, I want to find all tables in a view and similarly I want to check
which stored procs use a particular table or view.
Can you please let me know how I can find this.
Thanks
KarenQuickest and easiest but not necessarily 100% reliable is to query
SYSCOMMENTS:
SELECT DISTINCT OBJECT_NAME(id)
FROM syscomments
WHERE text LIKE '%object_name%' ;
Better is to search the source code in your source control system. I'm
assuming you do have source control. You certainly ought to have if you do
any kind of SQL development.
David Portas
SQL Server MVP
--
<karenmiddleol@.yahoo.com> wrote in message
news:1128990708.992145.32340@.g47g2000cwa.googlegroups.com...
>I want to find all views using a particular table name.
> Also, I want to find all tables in a view and similarly I want to check
> which stored procs use a particular table or view.
> Can you please let me know how I can find this.
> Thanks
> Karen
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:89OdnaebcPHXktbenZ2dnUVZ8qmdnZ2d@.gi
ganews.com...
> Quickest and easiest but not necessarily 100% reliable is to query
> SYSCOMMENTS:
> SELECT DISTINCT OBJECT_NAME(id)
> FROM syscomments
> WHERE text LIKE '%object_name%' ;
> Better is to search the source code in your source control system. I'm
> assuming you do have source control. You certainly ought to have if you do
> any kind of SQL development.
Wouldn't sysdepends be better?
Michael|||The easiest way is to use the INFORMATION Schema Views:
SELECT view_name
FROM INFORMATION_SCHEMA.View_table_usage
WHERE Table_name = 'SomeTableName'
HTH, Jens Suessmeyer.|||Sysdepends isn't totally reliable. It isn't always updated because of
deferred name resolution. I'd say that searching the source code should
be the most reliable method.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129015487.553756.55240@.o13g2000cwo.googlegroups.com...
> Sysdepends isn't totally reliable. It isn't always updated because of
> deferred name resolution. I'd say that searching the source code should
> be the most reliable method.
Just drop and recreate the query and sysdepends will be accurate. I have a
routine that does this for every object in the database to make sysdepends
accurate throughout the database.
Michael

how to find invalid objects --After DDL changes

Hi

I have a SQL Server 2005 database running. When I run some ddl changes, I want to find all the procs/objects that get invalidated because of object not found error.....

Is there any way that I can look up in sysdepends or other tables to find information about this.

Regards

Imtiaz

You can query the system view sys.syscomments for any procedures or functions that reference the objects. The text of each is stored in sys.syscomments.text.