Error while storing results of sp_depends in a new table

Question

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

Answer 1

0

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.

answered Jul 08 '11 at 08:03 AM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

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 '11 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 '11 at 09:07 AM AaronBertrand

Sorry.. forgot to mention SQL server 2005 version at the start of the question.

Jul 08 '11 at 09:09 AM anjaliv

If using SQL 2005, then use the sys.sql_dependencies system view

Jul 08 '11 at 09:21 AM Kev Riley ♦♦

(comments are locked)

Answer 2

0

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.

answered Jul 08 '11 at 08:10 AM

SirSQL
4.6k ● 1 ● 3

(comments are locked)

Error while storing results of sp_depends in a new table

Follow this question

Related Questions