asked
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
`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
If using SQL 2005, then use the sys.sql_dependencies system view
This is why it is important to specify the version and edition you are using as part of the question.
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.
