Hi
I have a problem. I have a database with way to many stored procedures. In
order delete some i made a script that finds stored procedures and their
dependencies. Those without any dependencies should be deleted because they
typically have been emptied for logic.
But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
uses a normal table or a table variable there is a dependency.
My example below turns out to have no dependecies as all even though the
table exists up front and the procedure has been executed succesfully.
Example: --
CREATE PROCEDURE Test3 AS
Create Table #no (
i int
)
Insert into #no (i)Select top 10 ID from table_1
Select * from #no
Drop table #no
This is a problem for me because a lot of my reports collect data from
several tables and insert them into temp tables. But effectively they will
show up with no dependencies and therefor be subject for deletion.
Remodelling all stored procedures to use table variables is no option.
This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error in
the product or whether there is a good reason for doing as it does.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
One of the reasons dependency information may be missing is due to deferred
name resolution. Since the temp table does not exist when the proc is
created, no dependency info is recorded for any objects referenced by the
INSERT statement.
> Remodelling all stored procedures to use table variables is no option.
If you create the temp table before creating the proc, you should get the
dependency info.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
Although one could make the argument that this is a bug, it's unclear to me
what the correct behavior should be when a statement contains a mix of
existing and non-existing objects. You might consider submitting product
enhancement feedback via Connect (http://connect.microsoft.com/SQLServer) to
suggest that known dependency information be recorded when a statement
contains a mix of existing and non-existing objects. Perhaps this will make
it into the next SQL Server version or future service pack.
Hope this helps.
Dan Guzman
SQL Server MVP
"Karsten Feddersen, Denmark" <Karsten Feddersen,
Denmark@.discussions.microsoft.com> wrote in message
news:48D61C0B-5635-4137-A3E8-C7A7CC16796F@.microsoft.com...
> Hi
> I have a problem. I have a database with way to many stored procedures. In
> order delete some i made a script that finds stored procedures and their
> dependencies. Those without any dependencies should be deleted because
> they
> typically have been emptied for logic.
> But unfortunately if there is a 'INSERT INTO #TEMP (FIELD1) SELECT Field1
> FROM TABLE_1' there is no dependency for table TABLE_1. If the INSERT INTO
> uses a normal table or a table variable there is a dependency.
> My example below turns out to have no dependecies as all even though the
> table exists up front and the procedure has been executed succesfully.
> Example: --
> CREATE PROCEDURE Test3 AS
> Create Table #no (
> i int
> )
> Insert into #no (i) Select top 10 ID from table_1
> Select * from #no
> Drop table #no
> --
> This is a problem for me because a lot of my reports collect data from
> several tables and insert them into temp tables. But effectively they will
> show up with no dependencies and therefor be subject for deletion.
> Remodelling all stored procedures to use table variables is no option.
> This occurs in both SQL 2000 and SQL 2005 so I wonder if this is an error
> in
> the product or whether there is a good reason for doing as it does.
day.