Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
doco
Yak Posting Veteran
77 Posts |
 Posted - 2011-08-12 : 13:35:56
|
Its seems rather tedious to create a view of a table in another database and have to type out all the field names of that table when and exact copy is what is desired.Is there a way to do such a thing without having to type out all the table names? CREATE VIEW MyTable( EntireFieldList ) AS ( SELECT * FROM ThisDataBase.dbo.MyTable ) TIAEducation is what you have after you've forgotten everything you learned in school |
|
|
doco
Yak Posting Veteran
77 Posts |
Posted - 2011-08-12 : 13:42:14
|
| BTW:I am using the [ Script Table As... ] Select and then copying the list to paste, etc. Just curious if there is an automated process of doing the same thing in a script.Education is what you have after you've forgotten everything you learned in school |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-08-12 : 14:05:37
|
| Do you have to use views for security reasons? Perhaps synonyms might be easier for you. |
|
|
|
doco
Yak Posting Veteran
77 Posts |
Posted - 2011-08-12 : 14:40:24
|
Its not security. I am part of a five county consortium that uses the same front end software. However, we use the same DBO/IS folks for maintenance, report writing etc. Within each SQL Server instance there are of course, several databases. One of which for four of the five counties is set up with users having read/write permissions,etc. So, when creating views, functions, procs, etc. the tables accessed need to be fully qualified from that custom database EG SELECT p.*, v.* FROM CamaDatabase..parcel_base p JOIN AssessmentDB..val_component v ON p.lrsn = v.property_id WHERE p.status = 'A' AND v.tax_year = 2011 AND v.value_type = 'TVR' (pretty cheesy example but I think it gives the idea)Now the issue is our DBA/IS does not want the database qualification when discussing or sharing queries. Therefore, views with the same name as the original table are needed, etc. Its a little more involved than that but ...Education is what you have after you've forgotten everything you learned in school |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-08-12 : 14:53:18
|
| If you're on SQL 2005 or higher then synonyms should work better:USE OtherDBCREATE SYNONYM MyTable FOR ThisDataBase.dbo.MyTable |
|
|
|
|
|
|
|
|
CREATE VIEW - EXACT TABLE COPY