November 16, 2008 at 5:05 am
I need to solve the problem to execute sp_addextendedproperty with a new description for a column but that does in all the databases (more than 20) for typical the record created_id, deleted_id, updated_id….
I have proven it with sp_MSForeachDB and with a cursor for the tables nested within a cursor for databases but always it gives some problem with "USE @database".
This he would be the one that I believe that it would have to work but there is some detail that forgets to me.
Thank you very much, I am new with Transact.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_AAA_INSERT_DESCRIPTION_BY_SELECT_COLUMN]
@pCOLUMN VARCHAR(100),
@pDESCRIPTION VARCHAR(255),
@pForced smallint = 0,
@pBD VARCHAR(100) = NULL,
@pExceptBD VARCHAR(100) = NULL
/*
EXEC [SP_AAA_INSERT_DESCRIPTION_BY_SELECT_COLUMN] @pCOLUMN = 'CREATED_ID', @pDESCRIPTION = 'Record creation user', @pForced = 1
, @pBD = 'DBNAME'
, @pExceptBD = 'DBNAME'
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE @cError INT
DECLARE @cErrorMsg VARCHAR(8000)
DECLARE @cSPName SYSNAME
DECLARE @cTIENE_TRAN INT
DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE @strTable nvarchar(200)
DECLARE @strDescription nvarchar(2000)
SET @cError = 0
SET @cTIENE_TRAN = @@TRANCOUNT
SET @cSPname = OBJECT_NAME(@@PROCID)
BEGIN TRY
IF @cTIENE_TRAN = 0
BEGIN
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION @cSPname
END
/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
DECLARE crsBD CURSOR FOR
SELECT [Name]
FROM MASTER.sys.sysdatabases
WHERE [Name] NOT IN ( 'Master', 'tempdb', 'model', 'msdb' )
AND ( @pBD IS NULL OR [Name] = @pBD )
AND ( @pExceptBD IS NULL OR [Name] <> @pExceptBD )
ORDER BY [Name]
OPEN crsBD
FETCH NEXT FROM crsBD INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Command = 'USE [' + @DB_Name + ']'--+ CHAR(13) + CHAR(10) +' GO '+ CHAR(13) + CHAR(10)
print @Command
EXEC ( @Command )
BEGIN TRY
DECLARE crsTable CURSOR FOR
SELECT OBJECT_NAME(c.object_id) AS [TableName], Cast( ex.value AS nVarchar ) AS [Description]
FROM sys.columns c
LEFT OUTER JOIN sys.extended_properties ex
ON ex.major_id = c.object_id
AND ex.minor_id = c.column_id
WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped') = 0
AND ( ex.value is null OR ( @pForced = 1 AND ex.value is not null))
AND OBJECT_NAME(c.object_id) NOT LIKE 'VIW_%'
AND c.name = @pCOLUMN
OPEN crsTable
FETCH NEXT FROM crsTable INTO @strTable, @strDescription
WHILE @@FETCH_STATUS = 0
BEGIN
--print @strTable
IF ISNULL( @strDescription, '' ) = ''
BEGIN
SET @Command = 'sp_addextendedproperty ''MS_Description'' ,''' + @pDESCRIPTION + ''', ''user'', ''dbo'', ''table'', ''' + @strTable + ''', ''column'', '''+@pCOLUMN + ''''
END
ELSE
BEGIN
SET @Command = 'sp_updateextendedproperty ''MS_Description'' ,''' + @pDESCRIPTION + ''', ''user'', ''dbo'', ''table'', ''' + @strTable + ''', ''column'', '''+@pCOLUMN+''''
END
print @Command
-- EXEC sp_executesql @Command
EXEC ( @Command )
FETCH NEXT FROM crsTable INTO @strTable, @strDescription
END
CLOSE crsTable
DEALLOCATE crsTable
print '... ' + @DB_Name + ' (final)'
print ' '
END TRY
BEGIN CATCH
SELECT @cError = @@ERROR
DEALLOCATE crsTable
SET @cErrorMsg = dbo.fGET_ERROR()
RAISERROR(@cErrorMsg,16,1) WITH LOG
END CATCH
FETCH NEXT FROM crsBD INTO @DB_Name
END
CLOSE crsBD
DEALLOCATE crsBD
/*-------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
IF @cTIENE_TRAN = 0 COMMIT TRANSACTION @cSPname
END TRY
BEGIN CATCH
SELECT @cError = @@ERROR
DEALLOCATE crsBD
IF @cTIENE_TRAN = 0 ROLLBACK TRANSACTION @cSPname
SET @cErrorMsg = dbo.fGET_ERROR()
RAISERROR(@cErrorMsg,16,1) WITH LOG
END CATCH
RETURN(@cERROR)
END
November 16, 2008 at 7:27 am
You shouldn't need "Use Database", if you are building qualified table name strings to include the DB name. Try that when you build @strTable. Assuming you are creating your tables with the dbo schema like most, the format is DBNAME.dbo.TABLENAME
"If the executed string contains a USE statement that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement has finished running." There is nothing magical in SQL server that knows you intend a series of dynamic sql commands to be part of a single job running in a single context. You have to build the entire string for the job you want to execute.
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/01c64ab0-9227-475a-a59a-ed80f9333042.htm
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 16, 2008 at 8:52 am
thanks, I am going to try it
Angel.-
November 16, 2008 at 11:46 am
Bob Hovious (11/16/2008)
You shouldn't need "Use Database", if you are building qualified table name strings to include the DB name.
Actually, quite a few (maybe even most) DDL commands and sProcs only work in the current database. "Create View", for instance.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 16, 2008 at 12:40 pm
Thanks, Barry.
I was just responding to Angel's question and his specific code, but of course you are correct that simply qualifying table names isn't enough with many DDL commands.
The second part of my answer dealt directly with the problem that he was executing "Use Database" all by itself, and recommended that he get everything into one dynamic SQL string. I should clarify that string should include a "USE" statement if the database is going to be different.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 23, 2011 at 7:16 am
Bit old. but may still be of use
The only way to use the use <dbname> in a cursor is by wrappingg it and any other commands that want to use <dbname> in a sql string and then use sp_executesql
declare @dbName varchar(255)
declare @strSQL nvarchar(2000)
declare ListDbs cursor
for
select name from master..sysdatabases
open ListDbs
fetch next
from ListDbs
into @dbName
while @@fetch_status = 0
begin
select @strSQL =
'
use ' + @dbname + '
select db_name()
'
exec sp_executesql @strSQL
print db_name()
fetch next
from ListDbs
into @dbName
end
close ListDbs
deallocate ListDbs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply