Scheduled Query when DBS are continuously added

  • 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;

  • 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