Need to set library for CREATE VIEW in EXEC @SQL script

Question

Need to set library for CREATE VIEW in EXEC @SQL script

chas.1

SSC Enthusiast

Points: 160
More actions
April 19, 2017 at 12:47 pm

#320558

I have an EXEC @sql script which creates a view.
Reason for the EXEC @sql script is that the library and schema are passed as parameters to the procedure.
The CREATE VIEW needs to be the first statement in the script
and I can't designate the library.
Is there a way to set the library within the script?
Chas

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

What do you mean by 'library'?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

If you're having to create views dynamically at run time, that suggests to me that the database design may be wrong. Please will you share a bit more information on what your application does and why you don't create the views at design time?

John

chas.1 SSC Enthusiast Points: 160 More actions · Answer 3

John Mitchell-245523 - Thursday, April 20, 2017 5:13 AM
If you're having to create views dynamically at run time, that suggests to me that the database design may be wrong. Please will you share a bit more information on what your application does and why you don't create the views at design time?
John

I have a procedures set up which allows users to enter specific table names from different databases/schemas.
If the views are not there, the procedure will create them.

chas.1 SSC Enthusiast Points: 160 More actions · Answer 4

John Mitchell-245523 - Thursday, April 20, 2017 5:13 AM
If you're having to create views dynamically at run time, that suggests to me that the database design may be wrong. Please will you share a bit more information on what your application does and why you don't create the views at design time?
John

chas.1 - Wednesday, April 19, 2017 12:47 PM
I have an EXEC @sql script which creates a view.
Reason for the EXEC @sql script is that the library and schema are passed as parameters to the procedure.
The CREATE VIEW needs to be the first statement in the script
and I can't designate the library.
Is there a way to set the library within the script?
Chas

GilaMonster - Wednesday, April 19, 2017 12:51 PM
What do you mean by 'library'?

Sorry, I meant database.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

chas.1 - Thursday, April 20, 2017 5:45 AM
John Mitchell-245523 - Thursday, April 20, 2017 5:13 AM
If you're having to create views dynamically at run time, that suggests to me that the database design may be wrong. Please will you share a bit more information on what your application does and why you don't create the views at design time?
John
I have a procedures set up which allows users to enter specific table names from different databases/schemas.
If the views are not there, the procedure will create them.

Again, if you're relying on your users' understanding of the tables in the database, probably bad design. You'll be left with hundreds or thousands of views in your database that are only used once. How does the procedure know how to create the views? Are the views simply a UNION of the tables specified by the user? If that's the case, you almost certainly need to normalise your database so that you don't have the same table repeated in different databases and schemas.

Going back to your original question, though, if you want to set the library (database) within the script, simply use a three-part naming scheme: MyDatabase.MySchema.MyTable.

John

chas.1 SSC Enthusiast Points: 160 More actions · Answer 6

John Mitchell-245523 - Thursday, April 20, 2017 5:59 AM
chas.1 - Thursday, April 20, 2017 5:45 AM
John Mitchell-245523 - Thursday, April 20, 2017 5:13 AM
If you're having to create views dynamically at run time, that suggests to me that the database design may be wrong. Please will you share a bit more information on what your application does and why you don't create the views at design time?
John
I have a procedures set up which allows users to enter specific table names from different databases/schemas.
If the views are not there, the procedure will create them.
Again, if you're relying on your users' understanding of the tables in the database, probably bad design. You'll be left with hundreds or thousands of views in your database that are only used once. How does the procedure know how to create the views? Are the views simply a UNION of the tables specified by the user? If that's the case, you almost certainly need to normalise your database so that you don't have the same table repeated in different databases and schemas.
Going back to your original question, though, if you want to set the library (database) within the script, simply use a three-part naming scheme: MyDatabase.MySchema.MyTable.
John

But CREATE VIEW only allows schema and table. I need to set the database, but the CREATE VIEW statement needs to be first in the batch.
Chas

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Chas

So from one database, you want to create a view in another database? That's not possible, as far as I know. Even if it were, imagine all the permissions you'd need in each other's databases - it would be a security nightmare. Is this something new you're designing, or are you attempting to bolt something on to an already bad design?

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

chas.1 - Thursday, April 20, 2017 5:45 AM
John Mitchell-245523 - Thursday, April 20, 2017 5:13 AM
If you're having to create views dynamically at run time, that suggests to me that the database design may be wrong. Please will you share a bit more information on what your application does and why you don't create the views at design time?
John
I have a procedures set up which allows users to enter specific table names from different databases/schemas.
If the views are not there, the procedure will create them.

This is an extremely unusual requirement, but I have seen it before - something to do with mailing lists for conferences. What exactly does this application do, out of interest?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

chas.1 SSC Enthusiast Points: 160 More actions · Answer 9

ChrisM@Work - Thursday, April 20, 2017 7:14 AM
chas.1 - Thursday, April 20, 2017 5:45 AM
John Mitchell-245523 - Thursday, April 20, 2017 5:13 AM
If you're having to create views dynamically at run time, that suggests to me that the database design may be wrong. Please will you share a bit more information on what your application does and why you don't create the views at design time?
John
I have a procedures set up which allows users to enter specific table names from different databases/schemas.
If the views are not there, the procedure will create them.
This is an extremely unusual requirement, but I have seen it before - something to do with mailing lists for conferences. What exactly does this application do, out of interest?

John,
I work in a place with many different databases. I have been creating several procedures using dynamic SQL and substituting database and schema.
That way many users can use the same procedure(s).
Chas