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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy