473,238 Members | 1,644 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

okay, I'm frustrated....how do I

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

Aug 21 '06 #1
1 2210
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/m...S,SQL.90).aspx
--

Aug 21 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

43
by: steve | last post by:
I am quite frustrated with php’s include, as I have spent a ton of time on it already... anyone can tell me why it was designed like this (or something I don’t get)? The path in include is...
3
by: MaryO | last post by:
I was hoping someone could help me with the OnNotInList function from Access. I downloaded this code below. I keep getting an error on the fAddToList – Sub or Function not defined. Please help...
6
by: garyusenet | last post by:
I have created a new project. and within this project as per the tutorial created an sql database. i have made a query which returns the result i need. 1. I cant SAVE THE QUERY? where on earth...
4
by: Ann | last post by:
I'm in a very frustrated situation now regarding to use jsp or asp.net. I'm a VBA programmer. Some of our web pages related with databases were setup in kind of old way , that is, in Access...
10
by: Don Wash | last post by:
Hi! I'm pretty frustrated learning ASP.NET. I'm learning ASP.NET by downloading samples from various ASP.NET websites and none of the samples worked so far. The SAME error was shown whenever I...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.