x

Error while storing results of sp_depends in a new table

I am getting the following error when run the below script.

Insert Error: Column name or number of supplied values does not match table definition.

DECLARE name_cur CURSOR FOR SELECT db_name, obj_name from table1 
DECLARE @tableName NVARCHAR (800)
DECLARE @dbName NVARCHAR(800)
DECLARE @sql NVARCHAR(900)
OPEN name_cur
CREATE TABLE [dbo].[Table_Dependency](
      [db_name] [nvarchar](50),
      [table_name] [nvarchar](50),
      [obj_name] [nvarchar](50),
      [obj_type] [varchar](50),)
FETCH name_cur INTO @dbName, @tableName 
WHILE @@Fetch_Status = 0
BEGIN 
            SET @sql = 'USE '+@dbName+' EXEC sp_depends '+@tableName
            insert into [dbo].[Table_Dependency] (obj_name, obj_type)
            EXEC (@sql)
            UPDATE [dbo].[Table_Dependency] SET db_name = @dbName ,table_name = @tableName WHERE db_name IS NULL
                 FETCH name_cur INTO @dbName, @tableName
END
more ▼

asked Jul 08, 2011 at 07:54 AM in Default

anjaliv gravatar image

anjaliv
21 2 2 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

sp_depends [returns 2 result sets][1], one has 5 columns, the second 2 columns.

You are trying to return 5 columns into 2 columns, when you specify

 insert into [dbo].[Table_Dependency] (obj_name, obj_type)

Also from BOL

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.

[1]: http://msdn.microsoft.com/en-us/library/ms189487.aspx
more ▼

answered Jul 08, 2011 at 08:03 AM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

I am finding all object dependencies on a table in SQL server 2005. It gives me a syntax error for sys.dm_sql_referenced_entities
Jul 08, 2011 at 09:02 AM anjaliv
This is why it is important to specify the version and edition you are using as part of the question.
Jul 08, 2011 at 09:07 AM AaronBertrand
Sorry.. forgot to mention SQL server 2005 version at the start of the question.
Jul 08, 2011 at 09:09 AM anjaliv
If using SQL 2005, then use the sys.sql_dependencies system view
Jul 08, 2011 at 09:21 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
sp_depends returns two result sets and so you can't handle it that way. I would recommend using sys.sql_expression_dependencies, sys.dm_sql_referencing_entities and sys.dm_sql_reference_entities instead. These system views are a lot easier to query and will give you the results you need.
more ▼

answered Jul 08, 2011 at 08:10 AM

SirSQL gravatar image

SirSQL
4.8k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1949
x58
x9

asked: Jul 08, 2011 at 07:54 AM

Seen: 1937 times

Last Updated: Jul 08, 2011 at 09:21 AM