July 7, 2008 at 12:30 am
G'day all!
I am finding myself creating quite a few views lately and wondered if there was a way I could use dynamic SQL to speed up the process and make it less fiddly.
I have 15 seperate databases which are for this purpose identical. I want to write a custom SELECT statement that is applied to each of the databases and have a view created. At the moment I copy and paste the select statement and unions manually, just changing the database name which is not a lot of fun.
I have created a table that holds all of the database names ready to be inserted into a string to have the view created, but I just cant get my head around the syntax. A cursor seems suited to this job from what I've read, but maybe someone can offer a better way? I can see the common suggestion to never use cursors if you can help it, and I don't want to form any unnecessary bad habits 🙂
Here is an example of one of my views;
SELECT [field1], [field2], [field3]
FROM database1.dbo.[Table]
UNION ALL
SELECT [field1], [field2], [field3]
FROM database2.dbo.[Table]
UNION ALL
SELECT [field1], [field2], [field3]
FROM database3.dbo.[Table]
and so forth all the way to 15... its a long way when you've got complex select statements, and especially when you have to amend them. Replace all is good, but its not great >.<
Any comments please! All suggestions welcome!
- Damien 🙂
July 7, 2008 at 12:49 am
DamienB (7/7/2008)
A cursor seems suited to this job from what I've read, but maybe someone can offer a better way? I can see the common suggestion to never use cursors if you can help it, and I don't want to form any unnecessary bad habits 🙂
That's probably one of the few things I would use a cursor for. Just don't get into the habit of using them for data manipulation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2008 at 12:52 am
Hi Gail 🙂
Thanks for the quick reply! Maybe I should have been more specific... I need help actually writing the code! >.<
- Damien
July 7, 2008 at 12:57 am
Ah. In that case, please could you post the schema for the table you have the DB names in, a couple of rows from that table and the result you would like to see.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2008 at 7:29 pm
It is pretty long winded so I'll include two databases so it will just be a copy and paste job across the rest of them.
CREATE TABLE [ceo_dbs] (
[db_id] [int] NULL ,
[db_name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[system] [int] NULL ,
[school] [int] NULL
) ON [PRIMARY]
GO
Database content:
db_name school
St Marys Primary 1
St Johns 1
St Josephs Colleg 1
So what I am trying to do is get the cursor to insert the @db_name variable for me in the following select statement until it runs out of databases, then creates the view.
create view v_student_addresses as
select s.first_name, s.surname, s.mail, m.address01, m.address02, m.suburb, m.postcode,
d.school_name
from @dbname.dbo.student as s
left join @db_name.dbo.mail as m on m.mkey = s.mail,
cross join school_data as d
and then union the results from another database.
Thank you for your time and interest! I could find very little on the net that I understood to get around this, so good chance other people with multiple identical DB's will benefit too 🙂
- Damien
July 7, 2008 at 7:39 pm
I just posted a way to do somethng very similar, without cursors:
Create Proc spLogin_OwnedObjects ( @login as SYSNAME ) As
--spLogin_OwnedObjects 'sa'
declare @sql varchar(MAX), @DB_Objects varchar(512)
Select @DB_Objects = ' L.name as Login, U.*
From %D%.sys.objects o
Join %D%.sys.database_principals u
ON Coalesce(o.principal_id, (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))
= U.principal_id
left join %D%.sys.server_principals L on L.sid = u.sid
'
Select @sql = 'SELECT * FROM
(Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
+ Replace(@DB_objects, '%D%', [name])
From master.sys.databases
Where [name] = 'master'
Select @sql = @sql + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '
+ Replace(@DB_objects, '%D%', [name])
From master.sys.databases
Where [name] != 'master'
Select @sql = @sql + ') oo Where Login = ''' + @login + ''''
print @sql
EXEC (@sql)
What you want to do is much simpler, so you should be able to adapt it from this. If you need help figuring it out, just say so.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 7, 2008 at 7:57 pm
OK, here is a much simplified version that should work for you:
Create Proc spCreate_MultiDBView As
declare @sql varchar(MAX)
Set @sql = 'CREATE VIEW v_student_addresses As
'
Select @sql = @sql + Case RowNum When 1 then '' Else 'UNION ALL ' End
+ 'select s.first_name, s.surname, s.mail, m.address01, m.address02, m.suburb, m.postcode,
d.school_name
from '+ db_name +'.dbo.student as s
left join '+ db_name +'.dbo.mail as m on m.mkey = s.mail,
cross join school_data as d
'
From (Select Row_Number() Over (Order by db_name) as RowNum, * From ceo_dbs) R
print @sql
EXEC (@sql)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 7, 2008 at 10:19 pm
G'day mate,
Thank you very much for getting me started! I had to alter the code because I'm using SQL2000.
alter Proc spCreate_MultiDBView As
declare @sql varchar(6000)
Set @sql = 'CREATE VIEW v_student_addresses As
'
Select @sql = @sql + Case ident When 1 then ' ' Else 'UNION ALL ' End
+ 'select s.first_name, s.surname, s.school_year
from '+ '['+db_name + ']'+'.dbo.st as s where s.status = ''full''
and (s.school_year = ''y10'' or s.school_year = ''Y03'') '
From (Select * From cent_schools) R
print @sql
EXEC (@sql)
It looked complicated trying to replicate the RowNum/Over functions in SQL2000 so I thought bugger it and added an ident column to the cent_schools table which holds my database names 🙂 The only other change I made was including the square brackets so the syntax didnt dump because of spaces etc.
Thanks very much to you and Gail for helping me out! and so quickly too! I will be getting a fair bit of use out of this one I assure you 😀
- Damien
July 8, 2008 at 3:16 pm
Thanks for the feedback Damien.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply