February 25, 2016 at 4:24 pm
Hello all,
My end goal is to write a procedure which can do both of the following:
Return all extended properties on the database level for all user databases
Return select extended properties on the database level for all user databases based on a search value
I feel like this could be a valuable tool for sorting and documenting databases within SQL Server itself. I am focused on the search feature first.
I have read that I should not rely on sp_MSforeachdb (and would possibly prefer not to), but I feel like I am close to my goal by using it. If I create the below procedure, run it, and paste the output after "EXECUTE master.sys.sp_MSforeachdb", it works. However, if I replace the "print" at the end of my procedure with "EXECUTE master.sys.sp_MSforeachdb", it fails.
The error is a fairly vague syntax error and I am at a loss. I would love a hand if this is easy for someone. I'm also open to other suggestions and guidance on the final product (including possible alternatives - I would love to not return empty lines that do not match the search). Thank you!
CREATE PROCEDURE dbo.listprop
@search as varchar(50)
AS
DECLARE @sqlCommand VARCHAR(2000)
SET @sqlCommand =
'
''USE [?]
IF DB_ID(''''?'''') > 4
BEGIN
SELECT DB_NAME() AS [DB], name, CONVERT(varchar(100),value) as value
FROM sys.extended_properties
where
class_desc = ''''DATABASE'''' and
CONVERT(varchar(100),value) like ''''%'+@search+'%''''
END'''
print @sqlCommand
GO
February 25, 2016 at 6:52 pm
do you think like this ? work for me without error , but dont have results
you had more '
DECLARE @search AS VARCHAR(50) = ''
DECLARE @sqlCommand VARCHAR(2000)
SET @sqlCommand ='USE [?]
IF DB_ID(''?'')>4
BEGIN SELECT DB_NAME() AS [DB], name, CONVERT(varchar(100),value) as value
FROM sys.extended_properties
WHERE class_desc = ''DATABASE''
and CONVERT(varchar(100),value)
like ''%'+@search+'%''END
ELSE SELECT''NULL'''
PRINT @sqlCommand
EXECUTE master.sys.sp_MSforeachdb @sqlCommand
GO
February 26, 2016 at 9:00 am
Yes! That works, thank you! I still get blank lines returned for the databases that do not match the search but at least it works now. I will see if there is a way to clean it up more. But maybe that is just what I will get with sp_MSforeachdb. Thank you for your help.
February 26, 2016 at 10:51 am
I got it!
DECLARE @search AS VARCHAR(50) = ''
DECLARE @sqlCommand VARCHAR(2000)
SET @sqlCommand ='USE [?]
IF (EXISTS (SELECT 1
FROM sys.extended_properties
WHERE class_desc = ''DATABASE'' and
CONVERT(varchar(100),value) like ''%'+@search+'%''))
BEGIN
SELECT DB_NAME() AS [DB], name, CONVERT(varchar(100),value) as value
FROM sys.extended_properties
WHERE class_desc = ''DATABASE'' and
CONVERT(varchar(100),value) like ''%'+@search+'%''
END
EXECUTE master.sys.sp_MSforeachdb @sqlCommand
GO
February 26, 2016 at 1:17 pm
February 26, 2016 at 1:47 pm
February 26, 2016 at 3:37 pm
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply