March 13, 2008 at 9:14 am
Does anyone know if its possible to script out all tables within a database - then script them as create views within another database? or do i just need to bite the bullet and do it manually !??
Aany ideas greatly appreciated.
Oraculum
March 13, 2008 at 9:22 am
You could script out the select statement on the table using management studio and then add the CREATE VIEW View_Name AS statement and reference to the tables in the other database in the FROM clause.
Gethyn Elliswww.gethynellis.com
March 13, 2008 at 12:41 pm
oraculum (3/13/2008)
Does anyone know if its possible to script out all tables within a database - then script them as create views within another database? or do i just need to bite the bullet and do it manually !??Aany ideas greatly appreciated.
Yes, I do it all the time. You just select table_name from INFORMATION_SCHEMA.tables, imbed it into a Create View like so:
[font="Courier New"]'CREATE VIEW dbo.' + table_name + ' AS
Select * from SourceDB.dbo.' + table_name
[/font]
Then concatenate all of them together, go to your TargetDB and execute that giant string that you just built.
Doing this is easy, the real tricks are:
1) you don't actually want ALL of the tables normally, so you need a way to distinguish and filter them and
2) How to name them in the other DB? if you just keep the original table name, you could easily run into naming conflicts.
[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]
March 13, 2008 at 4:25 pm
thanks i will try that next time..as with most client requests it had to be done, well, when i asked the question..!!! as always!
... so with a bit of an 'export list' and textpad insert 'create view' etc etc 'from otherdb.dbo.xxx' its worked like a dream...plus the same list to grant select and deny everything else, and a short term reporting database was created...! vola!
like your method though...just had an hour to create!
Oraculum
March 13, 2008 at 4:52 pm
Isn't that exactly what a sysnonym stands for ?
Check BOL
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply