July 28, 2011 at 9:02 am
I am running a command within a stored procedure that can't explicitly reference a database name so it can only run successfully when the current database is set properly.
in my stored procedure inside a while loop I have added the following code:
SET @sqlCmd = 'Use ' + @Database
EXEC sp_executesql @sqlCmd
PRINT db_name()
The print statement shows that the current db has not changed. I need a way to change the current db based on the value of @Database.
July 28, 2011 at 9:06 am
I dont think we have enough information. What are you trying to run?
You can use 3 part naming to reference something in another database like this.
database.dbo.object
Or you could create a procedure in the master database, name it "sp_....." and it will be accessible in all databases.
You'll have to give us the full info to be of more use.
July 28, 2011 at 9:17 am
Ok, here is the whole procedure
Error:
Msg 50000, Level 16, State 1, Procedure ApplyExtendedProperties, Line 145
Object is invalid. Extended properties are not permitted on 'dbo.MessageMask.ColumnMapping', or the object does not exist.
dbo.messagemask.columnMapping is the column that it's trying to apply the property to but in a different database from where the stored procedure is called.
ALTER PROCEDURE [dbo].[ApplyExtendedProperties]
AS
BEGIN
SET NOCOUNT ON
-- Variable declarations
DECLARE
@Database NVARCHAR(250)
,@PropName NVARCHAR(250)
,@NewPropValue NVARCHAR(250)
,@level0Type NVARCHAR(250) = NULL
,@level0Name NVARCHAR(250) = NULL
,@level1Type NVARCHAR(250) = NULL
,@level1Name NVARCHAR(250) = NULL
,@level2Type NVARCHAR(250) = NULL
,@level2Name NVARCHAR(250) = NULL
,@sqlCmd NVARCHAR(500)
--Error handling variables--
DECLARE
@$prog VARCHAR(50)
,@$errno INT
,@$errmsg VARCHAR(4000)
,@$proc_section_nm VARCHAR(50)
,@$row_cnt INT
,@$error_db_name VARCHAR(50)
,@$CreateUserName VARCHAR(128)
, -- last user changed the data
@$CreateMachineName VARCHAR(128)
, -- last machine changes-procedure were run from
@$CreateSource VARCHAR(128) ; -- last process that made a changes
--Initialize Error Handling variables--
SELECT
@$errno = NULL
,@$errmsg = NULL
,@$proc_section_nm = NULL
,@$prog = LEFT(OBJECT_NAME(@@procid), 50)
,@$row_cnt = NULL
,@$error_db_name = DB_NAME() ;
--=========
BEGIN TRY
--=========
-- set this value at the begining of any Try block to identify which section the
-- error occured in. this information will be logged in the Error Log table.
SET @$proc_section_nm = 'Main' ;
-- use the input rowset and cursor through the data adding property when there is a value for
-- new property and updating when there is a value for new and existing
DECLARE c CURSOR
FOR
SELECT
DatabaseName
,CASE WHEN LTRIM([Level0Type]) = '' THEN NULL
ELSE Level0Type
END
,CASE WHEN LTRIM([Level0Name]) = '' THEN NULL
ELSE Level0Name
END
,CASE WHEN LTRIM([Level1Type]) = '' THEN NULL
ELSE Level1Type
END
,CASE WHEN LTRIM([Level1Name]) = '' THEN NULL
ELSE Level1Name
END
,CASE WHEN LTRIM([Level2Type]) = '' THEN NULL
ELSE Level2Type
END
,CASE WHEN LTRIM([Level2Name]) = '' THEN NULL
ELSE Level2Name
END
,[PropertyName]
,[NewPropertyValue]
FROM
[DbDocumentation].[dbo].[DocumentTemplateData]
WHERE
DatabaseName NOT IN (
SELECT
dbName
FROM
[DbDocumentation].[dbo].ExcludeDatabase)
AND LTRIM(NewPropertyValue) <> ''
OPEN c
FETCH NEXT FROM c INTO @Database, @level0Type, @level0Name,
@level1Type, @level1Name, @level2Type, @level2Name, @PropName,
@NewPropValue
WHILE @@FETCH_STATUS <> -1
BEGIN
BEGIN TRY
-- this does not appear to work....
SET @sqlCmd = 'Use ' + @Database
EXEC sp_executesql @sqlCmd
PRINT db_name()
-------------------------------------
-- if add fails then do update in catch block
EXEC sp_addextendedproperty @PropName, @NewPropValue,
@level0Type, @level0Name, @level1Type, @level1Name,
@level2Type, @level2Name
END TRY
BEGIN CATCH
EXEC sp_updateextendedproperty @PropName,
@NewPropValue, @level0Type, @level0Name,
@level1Type, @level1Name, @level2Type, @level2Name
END CATCH
FETCH NEXT FROM c INTO @Database, @level0Type, @level0Name,
@level1Type, @level1Name, @level2Type, @level2Name,
@PropName, @NewPropValue
END
CLOSE c
DEALLOCATE c
IF @@TRANCOUNT > 0
BEGIN
COMMIT
END
RETURN 0
--========
END TRY
--========
BEGIN CATCH
--===========
CLOSE c
DEALLOCATE c
ROLLBACK TRAN
SET @$errmsg = ERROR_MESSAGE() ;
RAISERROR ( @$errmsg, 16, 1 ) ;
-- set the error if not set
IF (ISNULL(@$errno, 0) = 0)
SET @$errno = ERROR_NUMBER() ;
--========
END CATCH
--========
SET NOCOUNT OFF ;
RETURN @$errno ;
END
July 28, 2011 at 9:19 am
You didn’t specify what you are trying to do, so maybe there is a better way. In any case if you’ll run what ever you need to run in your dynamic SQL, it will work as you expected. Check the code bellow that is based on your code:
declare @SqlCmd nvarchar(200)
declare @Database varchar(200)
set @Database = 'tempdb'
SET @SqlCmd = 'Use ' + @Database + ' PRINT db_name()' --should show tmpedb
EXEC sp_executesql @SqlCmd
PRINT db_name() --should show the original db
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 28, 2011 at 9:26 am
declare @SqlCmd nvarchar(200)
declare @Database varchar(200)
set @Database = 'tempdb'
SET @SqlCmd = 'Use ' + @Database + ' PRINT db_name()' --should show tmpedb
EXEC sp_executesql @SqlCmd
PRINT db_name() --should show the original db
so what I'm seeing there is that tempdb only exists as the current database for the scope of the execute SqlCmd.
outside of that the current database is still the same.
Is that correct?
July 28, 2011 at 9:27 am
This all needs to be dynamic too...
PRINT db_name()
-------------------------------------
-- if add fails then do update in catch block
EXEC sp_addextendedproperty @PropName, @NewPropValue,
@level0Type, @level0Name, @level1Type, @level1Name,
@level2Type, @level2Name
Keep in mind the the server "starts" a new connection, execution the code, and then returns to the previous context. That's why you can't access a temp table you create within the dynamic sql from outside, nor the variables unless you use and output variable.
July 28, 2011 at 9:30 am
Ninja, that makes sense, just like what I stated a couple posts above regarding Adi's post.
July 28, 2011 at 9:44 am
Tom Van Harpen (7/28/2011)
Ninja, that makes sense, just like what I stated a couple posts above regarding Adi's post.
Ya too many answers in parallel, I should start answering with MAXDOP 1 so to not repost the same answer as someone else.
July 28, 2011 at 9:46 am
Ok, what is MAX DOP 1 ??
July 28, 2011 at 9:52 am
Tom Van Harpen (7/28/2011)
Ok, what is MAX DOP 1 ??
degree of parallelism.
So process only 1 thread at a time. 😉
July 28, 2011 at 9:56 am
got it, little slow today I guess.....
July 28, 2011 at 9:58 am
Tom Van Harpen (7/28/2011)
got it, little slow today I guess.....
Me too, don't rub it in! :hehe:
July 28, 2011 at 10:24 am
Thanks for the help everyone.
Here is my modified SP using dynamic sql for entire statement, works much better, although I'm not sure why I need the commit but without it there are uncommitted transactions. I'm guessing the try block automatically starts a transaction..perhaps.
CREATE PROCEDURE [dbo].[ApplyExtendedProperties]
AS
BEGIN
SET NOCOUNT ON
-- Variable declarations
DECLARE
@Database NVARCHAR(255)
,@PropName NVARCHAR(255)
,@NewPropValue NVARCHAR(255)
,@level0Type NVARCHAR(255) = NULL
,@level0Name NVARCHAR(255) = NULL
,@level1Type NVARCHAR(255) = NULL
,@level1Name NVARCHAR(255) = NULL
,@level2Type NVARCHAR(255) = NULL
,@level2Name NVARCHAR(255) = NULL
,@sqlCmd NVARCHAR(MAX)
--Error handling variables--
DECLARE
@$prog VARCHAR(50)
,@$errno INT
,@$errmsg VARCHAR(4000)
,@$proc_section_nm VARCHAR(50)
,@$row_cnt INT
,@$error_db_name VARCHAR(50)
,@$CreateUserName VARCHAR(128)
, -- last user changed the data
@$CreateMachineName VARCHAR(128)
, -- last machine changes-procedure were run from
@$CreateSource VARCHAR(128) ; -- last process that made a changes
--Initialize Error Handling variables--
SELECT
@$errno = NULL
,@$errmsg = NULL
,@$proc_section_nm = NULL
,@$prog = LEFT(OBJECT_NAME(@@procid), 50)
,@$row_cnt = NULL
,@$error_db_name = DB_NAME() ;
--=========
BEGIN TRY
--=========
-- set this value at the begining of any Try block to identify which section the
-- error occured in. this information will be logged in the Error Log table.
SET @$proc_section_nm = 'Main' ;
-- use the input rowset and cursor through the data adding property when there is a value for
-- new property and updating when there is a value for new and existing
DECLARE c CURSOR
FOR
SELECT
DatabaseName
,CASE WHEN LTRIM([Level0Type]) = '' THEN NULL
ELSE Level0Type
END
,CASE WHEN LTRIM([Level0Name]) = '' THEN NULL
ELSE Level0Name
END
,CASE WHEN LTRIM([Level1Type]) = '' THEN NULL
ELSE Level1Type
END
,CASE WHEN LTRIM([Level1Name]) = '' THEN NULL
ELSE Level1Name
END
,CASE WHEN LTRIM([Level2Type]) = '' THEN NULL
ELSE Level2Type
END
,CASE WHEN LTRIM([Level2Name]) = '' THEN NULL
ELSE Level2Name
END
,[PropertyName]
,[NewPropertyValue]
FROM
[DbDocumentation].[dbo].[DocumentTemplateData]
WHERE
DatabaseName NOT IN (
SELECT
dbName
FROM
[DbDocumentation].[dbo].ExcludeDatabase)
AND LTRIM(NewPropertyValue) <> ''
OPEN c
FETCH NEXT FROM c INTO @Database, @level0Type, @level0Name,
@level1Type, @level1Name, @level2Type, @level2Name, @PropName,
@NewPropValue
WHILE @@FETCH_STATUS <> -1
BEGIN
BEGIN TRY
-- if add fails then do update in catch block
SET @sqlCmd = 'use ' + @Database
+ ' EXEC sp_addextendedproperty ''' + @PropName
+ ''',''' + @NewPropValue + ''',' + COALESCE(''''
+ @level0Type
+ ''',', 'NULL,')
+ COALESCE('''' + @level0Name + ''',', 'NULL,')
+ COALESCE('''' + @level1Type + ''',', 'NULL,')
+ COALESCE('''' + @level1Name + ''',', 'NULL,')
+ COALESCE('''' + @level2Type + ''',', 'NULL,')
+ COALESCE('''' + @level2Name + ''',', 'NULL')
EXEC sp_executesql @sqlCmd
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @sqlCmd = 'use ' + @Database
+ ' EXEC sp_updateextendedproperty ''' + @PropName
+ ''',''' + @NewPropValue + ''',' + COALESCE(''''
+ @level0Type
+ ''',', 'NULL,')
+ COALESCE('''' + @level0Name + ''',', 'NULL,')
+ COALESCE('''' + @level1Type + ''',', 'NULL,')
+ COALESCE('''' + @level1Name + ''',', 'NULL,')
+ COALESCE('''' + @level2Type + ''',', 'NULL,')
+ COALESCE('''' + @level2Name + ''',', 'NULL')
EXEC sp_executesql @sqlCmd
COMMIT TRANSACTION
END CATCH
FETCH NEXT FROM c INTO @Database, @level0Type, @level0Name,
@level1Type, @level1Name, @level2Type, @level2Name,
@PropName, @NewPropValue
END
CLOSE c
DEALLOCATE c
RETURN 0
--========
END TRY
--========
BEGIN CATCH
--===========
CLOSE c
DEALLOCATE c
ROLLBACK TRANSACTION
SET @$errmsg = ERROR_MESSAGE() ;
RAISERROR ( @$errmsg, 16, 1 ) ;
-- set the error if not set
IF (ISNULL(@$errno, 0) = 0)
SET @$errno = ERROR_NUMBER() ;
--========
END CATCH
--========
SET NOCOUNT OFF ;
RETURN @$errno ;
END
July 28, 2011 at 10:37 am
If anyone is interested here are the rest of the pieces...
I use the stored procedure (below) to load this table. then I export the table to Excel (using ssis). We can then add descriptions to db objects.
then upload the excel back into the following table (using ssis and replacing the data) then run the apply extended props procedure (prev post)
This cycle will run weekly and whenever we have time (maybe an hour a week) just go through the spreadsheet and enter what we can.
Then we can run RedGate SQL Docs and it builds the documentation with the descriptions. There is a command line interface so we can automate the documentation builds as well. This will appease the documentation gods and I can move on with my career....
Table def for storing the doc template:
CREATE TABLE [dbo].[DocumentTemplateData](
[ServerName] [nvarchar](255) NOT NULL,
[DatabaseName] [nvarchar](255) NOT NULL,
[ObjectType] [nvarchar](255) NOT NULL,
[Level0Type] [nvarchar](255) NULL,
[Level0Name] [nvarchar](255) NULL,
[Level1Type] [nvarchar](255) NULL,
[Level1Name] [nvarchar](255) NULL,
[Level2Type] [nvarchar](255) NULL,
[Level2Name] [nvarchar](255) NULL,
[PropertyName] [nvarchar](255) NOT NULL,
[NewPropertyValue] [nvarchar](255) NULL,
[CurrentPropertyValue] [nvarchar](255) NULL
) ON [PRIMARY]
Stored procedure to load the doc template table
CREATE PROCEDURE [dbo].[BuildDocumentTemplate]
-- all datetimes values wiil be set with getdate()
AS
BEGIN
SET NOCOUNT ON
-- Variable declarations
--Error handling variables--
DECLARE
@$prog VARCHAR(50)
,@$errno INT
,@$errmsg VARCHAR(4000)
,@$proc_section_nm VARCHAR(50)
,@$row_cnt INT
,@$error_db_name VARCHAR(50)
,@$CreateUserName VARCHAR(128)
, -- last user changed the data
@$CreateMachineName VARCHAR(128)
, -- last machine changes-procedure were run from
@$CreateSource VARCHAR(128) ; -- last process that made a changes
--Initialize Error Handling variables--
SELECT
@$errno = NULL
,@$errmsg = NULL
,@$proc_section_nm = NULL
,@$prog = LEFT(OBJECT_NAME(@@procid), 50)
,@$row_cnt = NULL
,@$error_db_name = DB_NAME() ;
--=========
BEGIN TRY
--=========
-- set this value at the begining of any Try block to identify which section the
-- error occured in. this information will be logged in the Error Log table.
SET @$proc_section_nm = 'Main' ;
TRUNCATE TABLE DbDocumentation.dbo.DocumentTemplateData
INSERT INTO DbDocumentation.dbo.DocumentTemplateData
EXECUTE sp_msforeachdb 'USE ?
-- database
SELECT ss1.*
,COALESCE(ss2.[Current Property Value],'''') AS [Current Property Value]
FROM
(
SELECT
@@Servername AS ServerName
,DB_NAME() AS DataBaseName
,''Database'' AS ObjectType
,'''' AS level0Type
,'''' AS level0Name
,'''' AS level1Type
,'''' AS level1Name
,'''' AS level2Type
,'''' AS level2Name
,''Description'' AS [Property Name]
,'''' AS [New Property Value]
) ss1
LEFT JOIN
(SELECT CAST(VALUE AS NVARCHAR(max)) AS [Current Property Value]
FROM sys.extended_properties ep
WHERE ep.class = 0
AND UPPER(ep.name) = ''DESCRIPTION'') ss2
ON 1=1
'
INSERT INTO DbDocumentation.dbo.DocumentTemplateData
EXECUTE sp_msforeachdb 'USE ?
-- Schemas
SELECT
@@Servername AS ServerName
,DB_NAME() AS DataBaseName
,''Schema'' AS ObjectType
,''SCHEMA'' AS level0Type
,s.name AS level0Name
,'''' AS level1Type
,'''' AS level1Name
,'''' AS level2Type
,'''' AS level2Name
,''Description'' AS [Property Name]
,'''' AS [New Property Value]
,COALESCE(CAST(ep.value AS NVARCHAR(MAX)),'''') AS [Current Property Value]
FROM sys.schemas s
JOIN sys.database_principals p
ON s.principal_id = p.principal_id
LEFT JOIN sys.extended_properties ep
ON ep.major_id = s.schema_id
AND UPPER(ep.name) = ''DESCRIPTION''
WHERE p.type = ''U''
'
INSERT INTO DbDocumentation.dbo.DocumentTemplateData
EXECUTE sp_msforeachdb 'USE ?
-- Tables
SELECT
@@Servername AS ServerName
,DB_NAME() AS DataBaseName
,''Table'' AS ObjectType
,''SCHEMA'' AS level0Type
,s.name AS level0Name
,''TABLE'' AS level1Type
,t.name AS level1Name
,'''' AS level2Type
,'''' AS level2Name
,''Description'' AS [Property Name]
,'''' AS [New Property Value]
,COALESCE(CAST(ep.VALUE AS NVARCHAR(MAX)),'''') AS [Current Property Value]
FROM sys.tables t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
LEFT JOIN sys.extended_properties ep
ON ep.major_id = t.object_id
AND ep.minor_id = 0
AND UPPER(ep.name) = ''DESCRIPTION''
'
INSERT INTO DbDocumentation.dbo.DocumentTemplateData
EXECUTE sp_msforeachdb 'USE ?
-- Columns
SELECT
@@Servername AS ServerName
,DB_NAME() AS DataBaseName
,''Column'' AS ObjectType
,''SCHEMA'' AS level0Type
,s.name AS level0Name
,''TABLE'' AS level1Type
,t.name AS level1Name
,''COLUMN'' AS level2Type
,c.name AS level2Name
,''Description'' AS [Property Name]
,'''' AS [New Property Value]
,COALESCE(CAST(ep.value AS NVARCHAR(MAX)),'''') AS [Current Property Value]
FROM sys.columns c
JOIN sys.tables t
ON c.object_id = t.object_id
JOIN sys.schemas s
ON t.schema_id = s.schema_id
LEFT JOIN sys.extended_properties ep
ON ep.major_id = t.object_id
AND ep.minor_id = c.column_id
AND UPPER(ep.name) = ''DESCRIPTION''
WHERE t.type = ''U''
'
INSERT INTO DbDocumentation.dbo.DocumentTemplateData
EXECUTE sp_msforeachdb 'USE ?
-- Functions
SELECT
@@Servername AS ServerName
,DB_NAME() AS DataBaseName
,''Function'' AS ObjectType
,''SCHEMA'' AS level0Type
,s.name AS level0Name
,''FUNCTION'' AS level1Type
,o.name AS level1Name
,'''' AS level2Type
,'''' AS level2Name
,''Description'' AS [Property Name]
,'''' AS [New Property Value]
,COALESCE(CAST(ep.value AS NVARCHAR(MAX)),'''') AS [Current Property Value]
FROM sys.objects o
JOIN SYS.schemas s
ON o.schema_id = s.schema_id
LEFT JOIN sys.extended_properties ep
ON ep.major_id = o.object_id
AND ep.minor_id = 0
AND UPPER(ep.name) = ''DESCRIPTION''
WHERE type_desc LIKE ''%FUNCTION%''
'
INSERT INTO DbDocumentation.dbo.DocumentTemplateData
EXECUTE sp_msforeachdb 'USE ?
-- Function Parameters
SELECT
@@Servername AS ServerName
,DB_NAME() AS DataBaseName
,''Function Parameters'' AS ObjectType
,''SCHEMA'' AS level0Type
,s.name AS level0Name
,''FUNCTION'' AS level1Type
,o.name AS level1Name
,''PARAMETER'' AS level2Type
,p.name AS level2Name
,''Description'' AS [Property Name]
,'''' AS [New Property Value]
,COALESCE(CAST(ep.value AS NVARCHAR(MAX)),'''') AS [Current Property Value]
FROM sys.objects o
JOIN SYS.schemas s
ON o.schema_id = s.schema_id
JOIN sys.parameters p
ON o.object_id = p.object_id
LEFT JOIN sys.extended_properties ep
ON ep.major_id = o.object_id
AND ep.minor_id = p.parameter_id
AND UPPER(ep.name) = ''DESCRIPTION''
WHERE type_desc LIKE ''%FUNCTION%''
AND RTRIM(LTRIM(p.name)) <> ''''
'
INSERT INTO DbDocumentation.dbo.DocumentTemplateData
EXECUTE sp_msforeachdb 'USE ?
-- Stored Procedures
SELECT
@@Servername AS ServerName
,DB_NAME() AS DataBaseName
,''Procedure'' AS ObjectType
,''SCHEMA'' AS level0Type
,s.name AS level0Name
,''PROCEDURE'' AS level1Type
,P.name AS level1Name
,'''' AS level2Type
,'''' AS level2Name
,''Description'' AS [Property Name]
,'''' AS [New Property Value]
,COALESCE(CAST(ep.value AS NVARCHAR(MAX)),'''') AS [Current Property Value]
FROM sys.procedures p
JOIN sys.schemas s
ON p.schema_id = s.schema_id
LEFT JOIN sys.extended_properties ep
ON ep.major_id = p.object_id
AND ep.minor_id = 0
AND UPPER(ep.name) = ''DESCRIPTION''
'
INSERT INTO DbDocumentation.dbo.DocumentTemplateData
EXECUTE sp_msforeachdb 'USE ?
-- Stored Procedures Parameters
SELECT
@@Servername AS ServerName
,DB_NAME() AS DataBaseName
,''Procedure Parameters'' AS ObjectType
,''SCHEMA'' AS level0Type
,s.name AS level0Name
,''PROCEDURE'' AS level1Type
,P.name AS level1Name
,''PARAMETER'' AS level2Type
,pr.name AS level2Name
,''Description'' AS [Property Name]
,'''' AS [New Property Value]
,COALESCE(CAST(ep.value AS NVARCHAR(MAX)),'''') AS [Current Property Value]
FROM sys.procedures p
JOIN sys.parameters pr
ON p.object_id = pr.object_id
JOIN sys.schemas s
ON p.schema_id = s.schema_id
LEFT JOIN sys.extended_properties ep
ON ep.major_id = p.object_id
AND ep.minor_id = pr.parameter_id
AND UPPER(ep.name) = ''DESCRIPTION''
'
INSERT INTO DbDocumentation.dbo.DocumentTemplateData
EXECUTE sp_msforeachdb 'USE ?
-- Views
SELECT
@@Servername AS ServerName
,DB_NAME() AS DataBaseName
,''View'' AS ObjectType
,''SCHEMA'' AS level0Type
,s.name AS level0Name
,''VIEW'' AS level1Type
,v.name AS level1Name
,'''' AS level2Type
,'''' AS level2Name
,''Description'' AS [Property Name]
,'''' AS [New Property Value]
,COALESCE(CAST(ep.value AS NVARCHAR(MAX)),'''') AS [Current Property Value]
FROM sys.views v
JOIN sys.schemas s
ON v.schema_id = s.schema_id
LEFT JOIN sys.extended_properties ep
ON ep.major_id = v.object_id
AND ep.minor_id = 0
AND UPPER(ep.name) = ''DESCRIPTION''
'
INSERT INTO DbDocumentation.dbo.DocumentTemplateData
EXECUTE sp_msforeachdb 'USE ?
-- View Columns
SELECT
@@Servername AS ServerName
,DB_NAME() AS DataBaseName
,''View Column'' AS ObjectType
,''SCHEMA'' AS level0Type
,s.name AS level0Name
,''VIEW'' AS level1Type
,v.name AS level1Name
,''COLUMN'' AS level2Type
,c.name AS level2Name
,''Description'' AS [Property Name]
,'''' AS [New Property Value]
,COALESCE(CAST(ep.value AS NVARCHAR(MAX)),'''') AS [Current Property Value]
FROM sys.views v
JOIN sys.columns c
ON v.object_id = c.object_id
JOIN sys.schemas s
ON v.schema_id = s.schema_id
LEFT JOIN sys.extended_properties ep
ON ep.major_id = v.object_id
AND ep.minor_id = c.column_id
AND UPPER(ep.name) = ''DESCRIPTION''
'
RETURN 0
--========
END TRY
--========
BEGIN CATCH
--===========
SET @$errmsg = ERROR_MESSAGE() ;
RAISERROR (@$errmsg, 16, 1) ;
IF (ISNULL(@$errno, 0) = 0)
SET @$errno = ERROR_NUMBER() ;
--========
END CATCH
--========
SET NOCOUNT OFF ;
RETURN @$errno ;
END
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply