data dictionary

Hello,

I am working on a project to reverse engineer requirements for a
database. The database is sitting on a MS SQL Server. How would I
get a listing of all tables and columns within each table to help
create a data dictionary.

I have developed a script in Oracle to do this, but I am just not
familiar with MS SQL Server synthax.

Thanks,

HiteshHi Hitesh

I'm an Oracle developer as well, so sorry for the vagueness...

I think you want to use some stored procedures. sp_tables and sp_columns
are system stored procedures that return the same info as the User_Tables
and User_Tab_Columns views in Oracle.

Do you have the SQL Server client tools installed? Books Online
explains the use of these procedures.

Joe

"Hitesh" <zerocoo_@.hotmail.com> wrote in message
news:30ca6fbb.0405180920.93a5e7c@.posting.google.co m...
> Hello,
> I am working on a project to reverse engineer requirements for a
> database. The database is sitting on a MS SQL Server. How would I
> get a listing of all tables and columns within each table to help
> create a data dictionary.
> I have developed a script in Oracle to do this, but I am just not
> familiar with MS SQL Server synthax.
> Thanks,
> Hitesh|||On 18 May 2004 10:20:24 -0700, Hitesh wrote:

>Hello,
>I am working on a project to reverse engineer requirements for a
>database. The database is sitting on a MS SQL Server. How would I
>get a listing of all tables and columns within each table to help
>create a data dictionary.
>I have developed a script in Oracle to do this, but I am just not
>familiar with MS SQL Server synthax.
>Thanks,
>Hitesh

Hi Hitesh,

All information about all tables:

SELECT * FROM INFORMATION_SCHEMA.TABLES

All information about all columns:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||If you have Enterprise Manager, you can also create Database Diagrams
containing some or all of the tables.

"Hitesh" <zerocoo_@.hotmail.com> wrote in message
news:30ca6fbb.0405180920.93a5e7c@.posting.google.co m...
> Hello,
> I am working on a project to reverse engineer requirements for a
> database. The database is sitting on a MS SQL Server. How would I
> get a listing of all tables and columns within each table to help
> create a data dictionary.
> I have developed a script in Oracle to do this, but I am just not
> familiar with MS SQL Server synthax.
> Thanks,
> Hitesh|||thanks...someone also suggested using the following

/*P = Stored Procedure
U = Table
PK = Primary Key
V = View
TR = Trigger
the 'xtype' is the type of object it is */

select so.name as obj_name,
sc.name as colm_name,
so.xtype
from sysobjects so (nolock),
syscolumns sc (nolock)
where so.id *= sc.id
order by so.name, sc.name

the above command seems to be more comprehensive than using
information_schema. when run the above query, i get hidden system
tables and approximately 600 rows of data, where as the
information_schema comman queries 250+ rows. anyone know what is the
difference between these two? thanks

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<9enka0p0p32mn2smhjp5rgmc9an0c973af@.4ax.com>...
> On 18 May 2004 10:20:24 -0700, Hitesh wrote:
> >Hello,
> >I am working on a project to reverse engineer requirements for a
> >database. The database is sitting on a MS SQL Server. How would I
> >get a listing of all tables and columns within each table to help
> >create a data dictionary.
> >I have developed a script in Oracle to do this, but I am just not
> >familiar with MS SQL Server synthax.
> >Thanks,
> >Hitesh
> Hi Hitesh,
> All information about all tables:
> SELECT * FROM INFORMATION_SCHEMA.TABLES
> All information about all columns:
> SELECT * FROM INFORMATION_SCHEMA.COLUMNS
>
> Best, Hugo|||On 19 May 2004 09:16:57 -0700, Hitesh wrote:

>thanks...someone also suggested using the following
>/*P = Stored Procedure
>U = Table
>PK = Primary Key
>V = View
>TR = Trigger
>the 'xtype' is the type of object it is */
>select so.name as obj_name,
>sc.name as colm_name,
>so.xtype
>from sysobjects so (nolock),
>syscolumns sc (nolock)
>where so.id *= sc.id
>order by so.name, sc.name
>
>the above command seems to be more comprehensive than using
>information_schema. when run the above query, i get hidden system
>tables and approximately 600 rows of data, where as the
>information_schema comman queries 250+ rows. anyone know what is the
>difference between these two? thanks
>SELECT * FROM INFORMATION_SCHEMA.TABLES
>SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Hi Hitesh,

The query you issued against the system tables will also include stored
procedures, constraints, triggers etc. That should explain the different
number of rows.

There is nothing wring with directly querying the system tables if you
really know what you're doing and if you're not bothered by upward
compatibility. The INFORMATIION_SCHEMA views conform to the ANSI standard
for SQL. If the structure of the system tables is changed in a future
version, the INFORMATION_SCHEMA views will be changed as well, to ensure
ANSI compliance. You'll have to modify your queries against the system
tables yourself.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks for clarifying

Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<hgfna09uu6p3q9l5h1u4shr8uvq250pm4k@.4ax.com>...
> On 19 May 2004 09:16:57 -0700, Hitesh wrote:
> >thanks...someone also suggested using the following
> >/*P = Stored Procedure
> >U = Table
> >PK = Primary Key
> >V = View
> >TR = Trigger
> >the 'xtype' is the type of object it is */
> >select so.name as obj_name,
> >sc.name as colm_name,
> >so.xtype
> >from sysobjects so (nolock),
> >syscolumns sc (nolock)
> >where so.id *= sc.id
> >order by so.name, sc.name
> >the above command seems to be more comprehensive than using
> >information_schema. when run the above query, i get hidden system
> >tables and approximately 600 rows of data, where as the
> >information_schema comman queries 250+ rows. anyone know what is the
> >difference between these two? thanks
> >SELECT * FROM INFORMATION_SCHEMA.TABLES
> >SELECT * FROM INFORMATION_SCHEMA.COLUMNS
> Hi Hitesh,
> The query you issued against the system tables will also include stored
> procedures, constraints, triggers etc. That should explain the different
> number of rows.
> There is nothing wring with directly querying the system tables if you
> really know what you're doing and if you're not bothered by upward
> compatibility. The INFORMATIION_SCHEMA views conform to the ANSI standard
> for SQL. If the structure of the system tables is changed in a future
> version, the INFORMATION_SCHEMA views will be changed as well, to ensure
> ANSI compliance. You'll have to modify your queries against the system
> tables yourself.
> Best, Hugo

Customizing the report scheduling...

one of the requirements is that if the report takes a long time to run the user can start report processing and later
when that report is processed user can see that in his 'My reports' section,

What will be the best way to do this, my main concerns are -

1 How can I use scheduling to accomplish this.

2 How to save the report, as snappshot or what...

3. How can I find in my Web app. that report is ready or not and show a link to it.

There isn't an easy way to know how long a report will take to run if this is what you after. Other than that:

1. Sure but scheduled reports have limitations. Since they run in an unattended mode, they cannot use User!UserID and all parameters must have defaults.

2. I would see if subscribed delivery works for your users. In fact, your users can create their own subscriptions if they have the necessary rights.

3. Subscribed reports can be delivered via e-mail. You don't need to do anything. The user will automatically receive the report via e-mail.

|||

Hi Teo,

Thanks for the reply,

1 What if we are not using the User!userId in report and all other parameters are passed while creating the schedule?

one more thing is while creating the schedule using web service (CreateSchedule) we can send a link of the report in email(without attaching the report);to what that link points to and cant we use that with ReportViewer control to show report to user.

2. I think after the schedule runs and finish processing report report service would be updating some table to show that this schedule is completed or not.

3. Just got an idea that, can i save the report in an ftp location through schedule and pick the report from there to show to user?

hope that makes sense....

|||

1. The parameters needs to be set when creating the subscription not schedule. A schedule can be shared among subscriptions. As I mentioned, if you decide to use subcribed delivery, it will be the end user who will set the parameters. When the user clicks on the link, the report will be open in the ASP.NET ReportViewer control. In the case of a custom application, we address a similar requirement by having a custom job controller which would generate the report on the server as a snapshot and send the history ID to the application once the report is ready.

2. Again, there is a difference between a schedule and subscription (if this is what you would use). That said, you can use GetScheduleProperties API to get the last time the schedule is run.

3. A subscribed report can be delivered to a network share. Start creating a subscription and you will see that you have a choice between e-mail and network share delivery.

|||

Teo Lachev wrote:

When the user clicks on the link, the report will be open in the ASP.NET ReportViewer control.

Did'nt got which link you are talking about...one sent in email? how come it will be opened in reportviewer?

Teo Lachev wrote:

In the case of a custom application, we address a similar requirement by having a custom job controller which would generate the report on the server as a snapshot and send the history ID to the application once the report is ready.

I think thats what I am looking for, can you provide some links for this or some sample app.

And by using CreateSubscription API i can set the schedule also or will need something else for that?

|||

Did'nt got which link you are talking about...one sent in email? how come it will be opened in reportviewer?

When you set up an e-mail subscription you can configure it to send the report link instead of the entire report. The report will open up in the ASP.NET report viewer. Try it out http://localhost/reportserver?/AdventureWorks%20Sample%20Reports/Company%20Sales&rs:Command=Render

I think thats what I am looking for, can you provide some links for this or some sample app.

In our case, we don't use subscriptions. Instead, the job service generates the report as a snapshot

if (parameters != null) // set report parameters {

managementProxy.SetReportParameters(reportPath, parameters);

}

// Create the report snapshot so the user can browse the report

managementProxy.UpdateReportExecutionSnapshot(reportPath);

// Check if the report is configured to keep snapshots in history

bool keepExecutionShapshots = false;

bool result = managementProxy.GetReportHistoryOptions(reportPath, out keepExecutionShapshots, out schedule);

if (keepExecutionShapshots)

{

// history is automatically created, get the list of history runs

ReportHistorySnapshot[] history = managementProxy.ListReportHistory(reportPath);

Array.Sort(history, CompareReportHistoryByDate); // need to sorty by date since history runs may not be chronologically sorted

historyID = history[history.Length - 1].HistoryID; //grab the last history run

}

else

{

// explicitly create history snapshot

historyID = managementProxy.CreateReportHistorySnapshot(reportPath, out warnings);

}