July 13, 2011 at 10:19 am
Hi All,
I have created a stored procedure which finds dependent objects on a particular table.
I want to run this stored procedure for different database and for different tables.
I have created cursor for this.
But when I write USE @dbname, it tries to find the Stored Procedure in a @dbname and not the current database.
Can anybody please help me with how do I write this command in a cursor?
DECLARE name_cur CURSOR FOR SELECT db_name, obj_name from Stats_Usage WHERE last_user_update > '2011-06-01' ORDER BY db_name
DECLARE @tableName NVARCHAR (800)
DECLARE @dbName NVARCHAR(800)
DECLARE @sql NVARCHAR(900)
OPEN name_cur
FETCH name_cur INTO @dbName, @tableName
WHILE @@Fetch_Status = 0
BEGIN
SET @sql = 'USE '+@dbName +' EXEC proc_depend ' + @tableName
EXEC (@sql)
FETCH name_cur INTO @dbName, @tableName
END
CLOSE name_cur
DEALLOCATE name_cur
GO
Thank you!!
July 13, 2011 at 10:26 am
well it depends on your proc whether will work correctly or not, but you can call the proc via a 3 part naming convention, and explicitly naming the database and schema:
SET @sql = 'USE '+@dbName +'; EXEC master.dbo.proc_depend ' + @tableName
here i assumed the master database, but you can change that if it's not right.
does the proc assume compatibility views like sysobjects and systables, or is it using the newer sys.tables and sys.objects(if true, you'd need to make it a system proc.
Lowell
July 13, 2011 at 10:37 am
first thing i would try is a 'GO' statement after your 'use' statement.
correct sytax would be
use [dbname]
GO
exec [procname]
but i also think lowell's syntax works.
let use know when and how you succeed?
July 13, 2011 at 10:55 am
Lowell's solution worked. Thank you!
I am creating a table in a stored procedure I have written. But when I call it from the above query, it returns an error saying that table already exists. When we run a stored procedure, does the table gets created? I am confused.
July 13, 2011 at 11:03 am
for help with the table thing, you'll need to include the code that creates the table. you say it is created in a proc. when is the proc run? in which db? is it a temp table?
July 13, 2011 at 11:21 am
If your going to use this accross the board. Why don't you do away with the cursor and use sp_MSForEachDB.
July 13, 2011 at 1:13 pm
@bagofbirds-767347: Procedure is run only in the above script. and It is in my current T1DB. It does not matter whether its a temp table or not..it gives the same error even for non temp table.
@bopeavy: am not sure how sp_MSForEachDB will help in my case. But trying on it.
July 13, 2011 at 1:13 pm
@bagofbirds-767347: Procedure is run only in the above script. and It is in my current T1DB. It does not matter whether its a temp table or not..it gives the same error even for non temp table.
@bopeavy: am not sure how sp_MSForEachDB will help in my case. But trying on it.
July 13, 2011 at 1:17 pm
Even if your doing this only with in a database you could use sp_msforeachtable to execute across all tables. Only recommended this because you were looking for dependents which soulds as if it may be related to doing maintenance.
July 13, 2011 at 1:20 pm
anjaliv (7/13/2011)
Lowell's solution worked. Thank you!I am creating a table in a stored procedure I have written. But when I call it from the above query, it returns an error saying that table already exists. When we run a stored procedure, does the table gets created? I am confused.
we'd have to see the procedure, i think...if it is calling itself recursively, then any temp table created at a higher level exists and is available for procs that are called inside it...that might be the issue.
Lowell
July 13, 2011 at 2:20 pm
Procedure that is called inside the cursor is as following:
CREATE PROCEDURE [dbo].[sp_dependency3]
-- Add the parameters for the stored procedure here
@tableName nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE TABLE [dbo].[Table_Dependency1](
[db_name] [nvarchar](50),
[table_name] [nvarchar](50),
[obj_name] [nvarchar](50),
[obj_type] [varchar](50),
)
insert into [dbo].[Table_Dependency1] (obj_name, obj_type)
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE CHAR(39)+'%'+@tableName+'%'+CHAR(39)
UPDATE [dbo].[Table_Dependency3] SET db_name = DB_NAME ,table_name = @tableName WHERE db_name IS NULL
END
July 13, 2011 at 2:35 pm
lots of stuff doesn't look right here.
if you EVER call that proc more than once, it will fail because it creates a permenant table, but never drops it.
depending on how the proc gets called, you don't know which database the table would get created in.
you need to fix that as well.
the calling user might not have create table permissions, so it could fail due to that as well.
CREATE PROCEDURE [dbo].[sp_dependency3]
-- Add the parameters for the stored procedure here
@tableName NVARCHAR(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF NOT EXISTS(SELECT name FROM T1DB.dbo.sysobjects WHERE name = 'Table_Dependency1')
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = 'CREATE TABLE T1DB.[dbo].[Table_Dependency1] (
[db_name] nvarchar(100) NULL,
[table_name] nvarchar(100) NULL,
[obj_name] nvarchar(100) NULL,
[obj_type] varchar(50) NULL)'
PRINT @sql
EXEC(@sql)
END --if exists
--what if there was previous data? do you want to delete it?
INSERT INTO [T1DB].[dbo].[Table_Dependency1] (obj_name, obj_type)
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE CHAR(39)+'%'+@tableName+'%'+CHAR(39)
UPDATE [T1DB].[dbo].[Table_Dependency1] SET DB_NAME = DB_NAME() ,table_name = @tableName WHERE DB_NAME IS NULL
END --PROC
Lowell
July 13, 2011 at 2:49 pm
Thank you so much for the help Lowell 🙂
I am using that stored procedure in a cursor and the code is as following:
It gives an error saying " Could not find stored procedure"
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
FETCH name_cur INTO @dbName, @tableName
WHILE @@Fetch_Status = 0
BEGIN
print @dbName
SET @sql = 'USE '+@dbName +' EXEC sp_dependency3 ' + @tableName
EXEC (@sql)
FETCH name_cur INTO @dbName, @tableName
END
CLOSE name_cur
DEALLOCATE name_cur
GO
I am asking too much...But i really need this to work.
Thank you again for the help!!
July 13, 2011 at 3:53 pm
Try three-part naming to reference your stored proc instead of USE. You may also have had an issue because you were not surrounding @tableName with single quotes in your proc call.
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
FETCH name_cur INTO @dbName, @tableName
WHILE @@Fetch_Status = 0
BEGIN
PRINT @dbName
SET @sql = 'EXEC ' + QUOTENAME(@dbName) + '.dbo.sp_dependency3 N''' + @tableName + ''''
EXEC (@sql)
FETCH name_cur INTO @dbName, @tableName
END
CLOSE name_cur
DEALLOCATE name_cur
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 13, 2011 at 4:04 pm
bagofbirds-767347 (7/13/2011)
first thing i would try is a 'GO' statement after your 'use' statement.correct sytax would be
use [dbname]
GO
exec [procname]
but i also think lowell's syntax works.
let use know when and how you succeed?
As an aside "GO" is a client-specific batch separator. It is not actually a T-SQL command. You can change it to suit in SSMS if you like.
To demonstrate, this will not work:
EXEC ('
USE master ;
GO
SELECT *
FROM sys.tables ;
GO
');
Where this will:
EXEC ('
USE master ;
SELECT *
FROM sys.tables ;
');
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply