|
|
Posted by David Portas on 08/21/06 20:11
a_dba_used_to_oracle wrote:
> 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/ms130214(en-US,SQL.90).aspx
--
[Back to original message]
|