How to use ROUTINE_COLUMNS

How to use ROUTINE_COLUMNS

Post by Shimon Si » Wed, 12 Jun 2002 10:37:58



Can somebody explain how to use INFORMATION_SCHEMA.ROUTINE_COLUMNS .
When I do
Select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS
I get empty recordset with just fields.
Thanks a lot, Shimon.
 
 
 

How to use ROUTINE_COLUMNS

Post by Steve Kas » Wed, 12 Jun 2002 11:02:30


Shimon,

  Books Online has an article with all the details about
INFORMATION_SCHEMA.ROUTINE_COLUMNS.  It lists the
information about the columns of all table-valued functions accessible
to the current user in the current database.  I'm glad you asked - I never
knew it existed, and it might turn out to be useful some time!

Steve Kass
Drew University


> Can somebody explain how to use INFORMATION_SCHEMA.ROUTINE_COLUMNS .
> When I do
> Select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS
> I get empty recordset with just fields.
> Thanks a lot, Shimon.


 
 
 

How to use ROUTINE_COLUMNS

Post by Shimon Si » Wed, 12 Jun 2002 12:14:28


This is the sentence from BOL.

To retrieve information from this view, specify the fully qualified name of
INFORMATION_SCHEMA view_name.

What does it mean in code?

Shimon.


> Shimon,

>   Books Online has an article with all the details about
> INFORMATION_SCHEMA.ROUTINE_COLUMNS.  It lists the
> information about the columns of all table-valued functions accessible
> to the current user in the current database.  I'm glad you asked - I never
> knew it existed, and it might turn out to be useful some time!

> Steve Kass
> Drew University


> > Can somebody explain how to use INFORMATION_SCHEMA.ROUTINE_COLUMNS .
> > When I do
> > Select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS
> > I get empty recordset with just fields.
> > Thanks a lot, Shimon.

 
 
 

How to use ROUTINE_COLUMNS

Post by Steve Kas » Wed, 12 Jun 2002 13:04:01


Shimon,

 ROUTINE_COLUMNS is the view name for one of the
INFORMATION_SCHEMA views.  The "fully-qualified" name
means <database>.<owner>.<name>, so it could be

pubs.information_schema.routine_columns.  The reason you might
be seeing no rows is that you might not have any table-valued
functions in the database.  To see something,

select * from master.information_schema.routine_columns

Steve


> This is the sentence from BOL.

> To retrieve information from this view, specify the fully qualified name of
> INFORMATION_SCHEMA view_name.

> What does it mean in code?

> Shimon.



> > Shimon,

> >   Books Online has an article with all the details about
> > INFORMATION_SCHEMA.ROUTINE_COLUMNS.  It lists the
> > information about the columns of all table-valued functions accessible
> > to the current user in the current database.  I'm glad you asked - I never
> > knew it existed, and it might turn out to be useful some time!

> > Steve Kass
> > Drew University


> > > Can somebody explain how to use INFORMATION_SCHEMA.ROUTINE_COLUMNS .
> > > When I do
> > > Select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS
> > > I get empty recordset with just fields.
> > > Thanks a lot, Shimon.

 
 
 

How to use ROUTINE_COLUMNS

Post by Tibor Karasz » Wed, 12 Jun 2002 15:43:30


Steve,

In case you haven't seen this. I materialized the information schema definition tables in SQL
Server using the ansi specs and then modified them so they were the structure as the information
schema views. From there, I did some diagrams (make sure IE doesn't mess it up by resizing):

http://www.dbmaint.com/information_schema.html

(I guess I should expand the three ROUTINES tables to show more columns than they do today, as
there is a bit space left...)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


> Shimon,

>   Books Online has an article with all the details about
> INFORMATION_SCHEMA.ROUTINE_COLUMNS.  It lists the
> information about the columns of all table-valued functions accessible
> to the current user in the current database.  I'm glad you asked - I never
> knew it existed, and it might turn out to be useful some time!

> Steve Kass
> Drew University


> > Can somebody explain how to use INFORMATION_SCHEMA.ROUTINE_COLUMNS .
> > When I do
> > Select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS
> > I get empty recordset with just fields.
> > Thanks a lot, Shimon.

 
 
 

How to use ROUTINE_COLUMNS

Post by Shimon Si » Wed, 12 Jun 2002 20:24:00


Steve,
Does it work for SP? That is what I really need.
Shimon

> Shimon,

>  ROUTINE_COLUMNS is the view name for one of the
> INFORMATION_SCHEMA views.  The "fully-qualified" name
> means <database>.<owner>.<name>, so it could be

> pubs.information_schema.routine_columns.  The reason you might
> be seeing no rows is that you might not have any table-valued
> functions in the database.  To see something,

> select * from master.information_schema.routine_columns

> Steve


> > This is the sentence from BOL.

> > To retrieve information from this view, specify the fully qualified name
of
> > INFORMATION_SCHEMA view_name.

> > What does it mean in code?

> > Shimon.



> > > Shimon,

> > >   Books Online has an article with all the details about
> > > INFORMATION_SCHEMA.ROUTINE_COLUMNS.  It lists the
> > > information about the columns of all table-valued functions accessible
> > > to the current user in the current database.  I'm glad you asked - I
never
> > > knew it existed, and it might turn out to be useful some time!

> > > Steve Kass
> > > Drew University


> > > > Can somebody explain how to use INFORMATION_SCHEMA.ROUTINE_COLUMNS .
> > > > When I do
> > > > Select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS
> > > > I get empty recordset with just fields.
> > > > Thanks a lot, Shimon.

 
 
 

How to use ROUTINE_COLUMNS

Post by Steve Kas » Wed, 12 Jun 2002 20:35:13


Shimon,

  Unfortunately, ROUTINE_COLUMNS only lists the columns
of table-valued functions, not procedures.  Since procedures
can return multiple result sets and can be non-deterministic, it's
not likely this can be done.

Steve


> Steve,
> Does it work for SP? That is what I really need.
> Shimon


> > Shimon,

> >  ROUTINE_COLUMNS is the view name for one of the
> > INFORMATION_SCHEMA views.  The "fully-qualified" name
> > means <database>.<owner>.<name>, so it could be

> > pubs.information_schema.routine_columns.  The reason you might
> > be seeing no rows is that you might not have any table-valued
> > functions in the database.  To see something,

> > select * from master.information_schema.routine_columns

> > Steve


> > > This is the sentence from BOL.

> > > To retrieve information from this view, specify the fully qualified name
> of
> > > INFORMATION_SCHEMA view_name.

> > > What does it mean in code?

> > > Shimon.



> > > > Shimon,

> > > >   Books Online has an article with all the details about
> > > > INFORMATION_SCHEMA.ROUTINE_COLUMNS.  It lists the
> > > > information about the columns of all table-valued functions accessible
> > > > to the current user in the current database.  I'm glad you asked - I
> never
> > > > knew it existed, and it might turn out to be useful some time!

> > > > Steve Kass
> > > > Drew University


> > > > > Can somebody explain how to use INFORMATION_SCHEMA.ROUTINE_COLUMNS .
> > > > > When I do
> > > > > Select * from INFORMATION_SCHEMA.ROUTINE_COLUMNS
> > > > > I get empty recordset with just fields.
> > > > > Thanks a lot, Shimon.

 
 
 

1. Export Data using DTS Import using BULK INSERT

SQL Server 2000...

I am exporting data from a table to a comma delimitted text file using
DTS.  Actually, I exported using the export wizard then saved as a DTS
package.  I then went into the DTS designer and added a connection to
another server and a Bulk Insert Task object to import the data from the
text file to this new connection/server upon complettion of the export
from the original server.  Comprende?  Here's my problem....
If my importing machine goes down I'd like the export, which happens
every minute, to ADD to the text file, instead then file is OVERWRITTEN
every minute.  Therefore when my importing machine comes back up I've
lost all but the last minutes worth of data.  

Any suggestons/insight would be much appreciated.

Pat Reddy
MAVERICK TECHNOLOGIES

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2. OAS link C programm

3. Using VB to determine if a DSN uses integrated security

4. Cursor Functions

5. Using OPENROWSET to INSERT using a DSN

6. MSSQL fans

7. MAPI Spooler problem using mail using SQL Mail

8. TNSlistener service problem in Oracle8 on Win2000 beta3

9. Using SQL Server as a client using ODBC

10. Using ADO to make connection to SQL Server using NetBIOS Name

11. Problem using shape recordsets with stored procedure that uses a temp table

12. Using SQL to count as if using an array

13. Problem in using Microsoft Word Mail Merge using datasource feature