I've searched the MS SQL Server Books Online and this group.
I've been doing Oracle on UNIX for 12+ years, and now I have some SQL
Server databases.
I've discovered the INFORMATION_SCHEMA tables (just like the good ol'
dba_* views in
Oracle)
I've figured out how to use SQL2005 Management Studio.
What'd I'd like to know is - can SQL Server be used to write SQL like
Oracle can; e.g:
spool csyn.sql
set heading off
set pagesize 6000
select 'CREATE PUBLIC SYNONYM '||table_name||' FOR
SYSADM.'||TABLE_NAME||';'
from dba_tables where owner='SYSADM';
spool off
In SQL*Plus, the above will create a script to create public synonyms
for tables in a PeopleSoft database (tables in PeopleSoft multiply like
Tribbles in a storage bin of quadrotriticale...).
Yah, GUI's are fine...until you have thousands and thousands of tables
to deal with!
Thanks ever so mucha_dba_used_to_oracle wrote:
Quote:
Originally Posted by
Greetings, all!
>
>
I've searched the MS SQL Server Books Online and this group.
>
I've been doing Oracle on UNIX for 12+ years, and now I have some SQL
Server databases.
>
I've discovered the INFORMATION_SCHEMA tables (just like the good ol'
dba_* views in
Oracle)
>
I've figured out how to use SQL2005 Management Studio.
>
What'd I'd like to know is - can SQL Server be used to write SQL like
Oracle can; e.g:
>
spool csyn.sql
set heading off
set pagesize 6000
>
select 'CREATE PUBLIC SYNONYM '||table_name||' FOR
SYSADM.'||TABLE_NAME||';'
from dba_tables where owner='SYSADM';
>
spool off
>
In SQL*Plus, the above will create a script to create public synonyms
for tables in a PeopleSoft database (tables in PeopleSoft multiply like
Tribbles in a storage bin of quadrotriticale...).
>
Yah, GUI's are fine...until you have thousands and thousands of tables
to deal with!
>
Thanks ever so much
Try:
SELECT 'CREATE SYNONYM dbo.'
+QUOTENAME(table_name)+' FOR '
+QUOTENAME(table_schema)+'.'
+QUOTENAME(table_name)
FROM information_schema.tables
WHERE table_schema = 'SYSADM' ;
This example assumes you have a schema called SYSADM and that you want
the synonym to belong to the dbo schema. You can do without the
QUOTENAME functions if your table names are such that they don't need
quoting (essentially that means no spaces, punctuation or reserved
words.
Run the script in Text mode (CTRL+T or click the "Results to Text"
button on the toolbar). Then either cut-and-paste the result into the
query window or save it to a file (Right-click, Save As).
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--
