March 6, 2017 at 9:03 am
Greetz!
I have a conundrum which I need some ideas on how to solve. T-SQL is not my bread and butter so maybe this is trivial (hoping).
One caveat before I explain, I cannot add stored procedures, triggers or tables to the SQL Server I'm executing against.
I have an PowerShell script that executes in task scheduler a few times a day that executes a query against multiple databases and uploads the results into a SharePoint document library where I then format it for display.
An example of this query is:
SET @xmlbody = (
SELECT CyronDB,Ltrim(RTrim(PROC_SET_CODE)) AS ProcSet,Ltrim(RTrim(PROC_SET_NAME)) AS ProcName
FROM (
SELECT 'Cyron01' as CyronDB, PROC_SET_CODE, PROC_SET_NAME
FROM [Cyron01].[dbo].[PRSM]
WHERE PROC_SET_CODE != 'SYSTEM' and LTRIM(RTRIM(PROC_SET_NAME)) != ''
UNION
SELECT 'Cyron02' as CyronDB, PROC_SET_CODE, PROC_SET_NAME
FROM [Cyron02].[dbo].[PRSM]
WHERE PROC_SET_CODE != 'SYSTEM' and LTRIM(RTRIM(PROC_SET_NAME)) != ''
) procset FOR XML RAW ('ProcSet'), ROOT ('ProcSets')
);
SELECT @xmlbody
The issue that I am having is that Cyron03, Cyron04, etc, will come along unbeknownst to me and I will need to go in and update my sql query manually. Another server we have is up to 19 databases and growing.
What I would like to do is create a query that doesn't need someone to go in and manually add a new query whenever a new database comes online (which is unannounced), but rather would find those DBs automagically and execute against them accordingly. As you can see from my sample the queries are small and the only
Ideas? Thank you in advance.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
March 6, 2017 at 9:54 am
Looks like I found a way to do this.
Declare @dataFiles Table (databaseName Varchar(256))
Declare @SQL Nvarchar(Max), @databaseName Varchar(256)
Set @SQL = '';
Insert @dataFiles
select sd.name
from sys.sysdatabases sd
Where sd.name not in ('master','tempdb','model','msdb') AND (ISNUMERIC(RIGHT(sd.name,2)) = 1)
Declare cur Cursor For
Select databaseName
From @dataFiles
Open cur
Fetch Next
From cur
Into @databaseName
While @@Fetch_Status = 0
Begin
Set @SQL = @SQL + 'SELECT [' + @databasename + '] as CyronDB, PROC_SET_CODE, PROC_SET_NAME FROM [''' + @databasename + '''].[dbo].[PRSM] WHERE PROC_SET_CODE != ''SYSTEM'' and LTRIM(RTRIM(PROC_SET_NAME)) != '''' UNION'
Fetch Next
From cur
Into @databaseName
End
Select @SQL
--Exec sp_executeSQL @SQL
Close cur
Deallocate cur
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply