blossompark
asked on
SQL code to change specific value automatically (loop) in SELECT statement
Hi,
I have the following code
I need to run this against an installation of SQL Server which houses about 50 Databases.
I would like to code this so that it automatically changes the database name rather than me having to input each database name manually.
any guidance appreciated.
Thanks
I have the following code
USE [<database_name>] GO SELECT name AS Assembly_Name, permission_set_desc FROM sys.assemblies WHERE is_user_defined = 1; GO
This is used to test if CLR assemblies are in use for a database.I need to run this against an installation of SQL Server which houses about 50 Databases.
I would like to code this so that it automatically changes the database name rather than me having to input each database name manually.
any guidance appreciated.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect! gets me the answer, thank you...
BUT, I would like to do this maybe using a cursor as I have not done this before and feel it would help my development, i was looking at somehow using the list of table names and somehow plugging them into the test code perhaps using a variable....if you have any recommendations, (sites etc) that would help me on my way with this i would be grateful, i could also open another question...
thanks again for the answer
BUT, I would like to do this maybe using a cursor as I have not done this before and feel it would help my development, i was looking at somehow using the list of table names and somehow plugging them into the test code perhaps using a variable....if you have any recommendations, (sites etc) that would help me on my way with this i would be grateful, i could also open another question...
thanks again for the answer
E.g. as cursor
DECLARE @NO_ERROR INT = 0;
DECLARE @TEMPLATE NVARCHAR(MAX) = 'SELECT ''@DatabaseName'' as database_name, name AS Assembly_Name, permission_set_desc FROM @DatabaseName.sys.assemblies WHERE is_user_defined = 0;';
DECLARE @DatabaseName sysname;
DECLARE @Statement NVARCHAR(MAX);
DECLARE cr_Databases CURSOR FOR
SELECT D.name
FROM sys.databases D;
OPEN cr_Databases;
FETCH NEXT FROM cr_Databases
INTO @DatabaseName;
WHILE @@FETCH_STATUS = @NO_ERROR
BEGIN
PRINT @DatabaseName;
SET @Statement = REPLACE(@TEMPLATE, '@DatabaseName', QUOTENAME(@DatabaseName));
EXECUTE ( @Statement );
FETCH NEXT FROM cr_Databases
INTO @DatabaseName;
END;
CLOSE cr_Databases;
DEALLOCATE cr_Databases;
or as dynamic SQLDECLARE @TEMPLATE NVARCHAR(MAX) = 'SELECT ''@DatabaseName'' as database_name, name AS Assembly_Name, permission_set_desc FROM @DatabaseName.sys.assemblies WHERE is_user_defined = 0';
DECLARE @Statement NVARCHAR(MAX) = STUFF(( SELECT 'UNION ALL ' + REPLACE(@TEMPLATE, '@DatabaseName', QUOTENAME(name))
FROM sys.databases
FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,
1 ,
10 ,
'');
EXECUTE ( @Statement );
ASKER
Thanks Ste5an, that is far more than i expected, i will study these, Thank You!
ASKER
Open in new window
gets me list of all relevant databases