question

anjaliv avatar image
anjaliv 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
sql-server-2005cursordependency
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
`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
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If using SQL 2005, then use the sys.sql_dependencies system view
2 Likes 2 ·
This is why it is important to specify the version and edition you are using as part of the question.
1 Like 1 ·
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
0 Likes 0 ·
Sorry.. forgot to mention SQL server 2005 version at the start of the question.
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.