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

avatar 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, 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.

more ▼

answered Jul 08, 2011 at 08:03 AM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

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

avatar image

SirSQL
4.9k 4 5

(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.

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:

x2017
x68
x10

asked: Jul 08, 2011 at 07:54 AM

Seen: 2473 times

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

Copyright 2016 Redgate Software. Privacy Policy