Re: Meta data of query?

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 07/11/04 

Date: Sun, 11 Jul 2004 09:28:15 -0500

To add to David's response,

> Is it possible to access this meta data in the executing script from
> any of the sys tables. Would it be easier to parse the SQL statement.
>
> Also, where is the meta-data of a result set of a stored procedure
> stored or where or how is it obtained.

Column meta data is not persisted for ad-hoc queries and SQL statement
parsing is not trivial. However, meta data is returned to the client along
with the resultset and you can easily access it from you application (this
is what QA does).

Although you can get resultset meta data directly in Transact-SQL, one
workaround is to create a temp table with the result so that you can query
the temp table meta data that corresponds to the resultset. The example
below uses this technique along with SET FMTONLY as David suggested.

IF OBJECT_ID('tempdb..#MetaData') IS NOT NULL
    DROP TABLE #MetaData
--create temp table with resultset columns
SELECT *
INTO #MetaData
FROM
    OPENROWSET (
        'SQLOLEDB' ,
        'Trusted_Connection=Yes;Database=tempdb',
        'SET FMTONLY ON SELECT * FROM personal_details SET FMTONLY OFF'
    )
--list meta data
SELECT *
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
    OBJECT_ID(QUOTENAME('dbo') + '.' + QUOTENAME(TABLE_NAME)) =
    OBJECT_ID('tempdb.dbo.#MetaData')
ORDER BY ORDINAL_POSITION
DROP TABLE #MetaData 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"LCaffrey" <lxc@eosys.ie> wrote in message
news:4a6dded0.0407110314.7a79902@posting.google.com...
> Hi,
>
> Take a typical query...
>
> SELECT col1 as "my_first_name"
>       ,col2 as "my_second_name"
>       ,col3 as "my_telephone_no"
>   FROM personal_details
>  WHERE col2 = 'Caffrey'
>
> When this is run in Query Analyzer or the SQL Window of EE, the column
> headers are automatically given in the result set.
>
> Is it possible to access this meta data in the executing script from
> any of the sys tables. Would it be easier to parse the SQL statement.
>
> Also, where is the meta-data of a result set of a stored procedure
> stored or where or how is it obtained.
>
> Regards
>
> Liam Caffrey
> lxc@eosys.ie

Relevant Pages

  • Re: Stored Procedures v Views
    ... query draws the data from 4 tables. ... Do I use a stored proc or a view? ... > Just dynamically build a SQL statement using some other tool (like your VBA ... > this interface, even if your stored procedure is poorly written, you can ...
    (microsoft.public.sqlserver.programming)
  • Re: Cross Database Join, C++ program set up confusion
    ... the SQL statement I was trying to use in my CRecordset class in Query ... feeling that if I can get a stored procedure to generate a dynamic ... I found in MSDN documentation regarding CRecordset: ... If your tables are on the same data source, try the link I sent in my ...
    (microsoft.public.vc.database)
  • Re: Stored Procedures v Views
    ... "Louis Davidson" wrote: ... > Just dynamically build a SQL statement using some other tool (like your VBA ... > this interface, even if your stored procedure is poorly written, you can ... The code dynamically generates a SQL statement in VBA, ...
    (microsoft.public.sqlserver.programming)
  • Cont
    ... I created a new stored procedure: ... >Private Sub Command2_Click ... >> My form is bound to a simple SQL statement. ...
    (microsoft.public.access.formscoding)
  • Re: Stored Procedures v Views
    ... Build a stored procedure, ... The goal here will be to not use any dynamic sql like you used in Access. ... If the query is very complex, but frankly, if you have to do this, you are ... > they make are then used to create the SQL statement which is then used to ...
    (microsoft.public.sqlserver.programming)