View same table from all databases? 2

[misscrf]

SELECT  name AS DatabaseName
from master.dbo.sysdatabases
where sid <> 0x01

Now I just want to build a union like this:

select DatabaseName, name as CollectionName, oid as CollectionID
from [DB1].dbo.objects  
where sclid = -1
union all
select DatabaseName, name as CollectionName, oid as CollectionID
from [db2].dbo.objects  
where sclid = -1

I want the databases from the first query to loop through the variable of the [db1] [db2] with the DatabaseName column in the union.

That way I get accurate data each time it runs.

Any thoughts on how to accomplish this please?



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

quick update. I got the exclusion of the ismb to work. I made it where name <> 'ismbtemp' rather than '+name +' <> 'ismbtemp' . I also put it in both unions even though that db is only on one of the servers.

Next is to make this a view, then a stored proceedure and then a job. My mind is going to explode with how much I am learning! lol

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[bborissov]

I doubt that all databases you want to check have different names. at least they should begin with same chars, right?
If so than instead of using:
WHERE Name <> 'ismbtemp' I would use:

WHERE Name LIKE 'CharsYourDBsBegins%'


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

I'm afraid you would not be able to incorporate this dynamic query into a view - do you really need to? Can you keep it as SP?

 

[misscrf]

I already did lol. I created a view from the result of the script, and then added the alter view statement at the beginning of this and tested it. I created all of that as a stored procedure that will execute altering the view. I have it set to run the stored proc once a week (which is probably more often than I need to). I need the view, because I need to be able to call it like a table or query in an Access front end where I am building forms for end users to enter data into.

This is going to come in handy for a lot of views that I need for the kind of servers and dbs I work with. Thanks again to everyone for all of your help!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[gmmastros]

I have it set to run the stored proc once a week (which is probably more often than I need to).

You could probably create a server level DDL trigger that fires whenever you create a new database. This trigger could call the stored procedure that creates the view. This way, you won't be running the procedure more often than you need to, and more importantly, you won't need to wait (at most) a week for the new database to be included.

You'll probably want to create a server level trigger for this. In the thread I reference below, I show how to create a database level trigger that fires whenever there is a change to a table structure.

thread183-1577150



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[misscrf]

Thanks!!! I will look into that.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Hey, everyone. I have taken this script and attempted to create another version of it for other uses. I found that the varchar(max) declaration has a max of 8000 characters and I am running into that. Any other type I can declare the @sql as so that I don't run into that problem?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[gmmastros]

Nah. varchar(max) can store approximately 2 billion characters. There must be something else going on. Can you show us your code?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

It is not correct - the problem is that print output can only show first 8000 chars. Boris solved this problem for me with clever looping before. Can you save the result into the table instead of trying to print it?

 

[misscrf]

This is the code I am working on.

SET NOCOUNT ON

DECLARE @sql nvarchar(max)

SET @sql = 'alter view vw_ABC as ' +''

SELECT @sql = @sql  + ' select ''[Server1].'+''+Name+''' as DatabaseName,E.Directory, E.Title, E.Created, E.WorkStation, 
E.Status, E.ItemCount, E.ItemTotal, E.RecordsWritten, E.Errors, E.Excluded, 
E.PartiallyExcluded, E.Incomplete, E.Size
FROM [Server1].['+Name+'].dbo.tblTable1 E
UNION ALL
'
from [Server1].master.dbo.sysdatabases
where sid <> 0x01 
and name <> 'BatchControl' 
and name <> 'Management' 



SELECT @sql = @sql  + 'select ''[Server2].'+''+Name+''' as DatabaseName,E.Directory, E.Title, E.Created, E.WorkStation, 
E.Status, E.ItemCount, E.ItemTotal, E.RecordsWritten, E.Errors, E.Excluded, 
E.PartiallyExcluded, E.Incomplete, E.Size
FROM [Server2].['+Name+'].dbo.tblTable1 E
UNION ALL
'
from [Server2].master.dbo.sysdatabases
where sid <> 0x01 
and name <> 'BatchControl'
and name <> 'Management' 

SET @sql = left(@sql,LEN(@sql)-11)

exec (@sql)

The first server select only spits out the first database select statement, then all the database select statements for the second server come out fine. It is just not giving me all of the first.

I am not sure why the first one come out. I changed it to an exec so it would just run instead of printing to text. I can't throw it into a table, because I am creating(updating/altering) a view with this.

Thanks all.

misscrf

It is never too late to become what you could have been ~ George Eliot