Get column metadata from arbitrary query returning single resultset?

Question

question

Martin 1 asked Jul 13, '11

Get column metadata from arbitrary query returning single resultset?

Is there any good way in TSQL (or in SSMS itself) of determining the column metadata for a resultset? I needed to insert the result of `xp_readerrorlog` into a table variable but wasn't sure what the declaration of that should be. I ended up using SELECT TOP 0 * INTO #T FROM OPENROWSET('SQLNCLI','Server=.\MSSQL2008;Trusted_Connection=yes;', 'SET NOCOUNT ON;SET FMTONLY OFF;EXEC sys.xp_readerrorlog') SELECT c.name, c.is_nullable, t.name, c.max_length,c.precision,c.scale FROM tempdb.sys.columns c JOIN sys.types t ON t.user_type_id = c.user_type_id WHERE object_id = object_id('tempdb..#T') Which indicates that the definition of the table should be DECLARE @xp_readerrorlog TABLE ( LogDate datetime, ProcessInfo nvarchar(12), [Text] varchar(3999) ) Is there a better way?

sql-server ssms metadata

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bopeavy commented · Jul 14, 2011 at 05:47 AM

The difference is what ever you want to do can change how you need to approach the problem itself. So I was only asking to see how I could help.

2 ·

bopeavy commented · Jul 13, 2011 at 12:36 PM

What is it you want to accomplish? Are you doing something with the data from the error log, storing it, send notification or what?

0 ·

Martin 1 commented · Jul 13, 2011 at 12:51 PM

What difference does it make? I'm not asking anything specifically about `xp_readerrorlog` itself. It's a general question as this type of need crops up from time to time in admin scripts (also with storing DBCC results to table) and I'd be interested to see other approaches.

0 ·

Martin 1 commented · Jul 14, 2011 at 04:54 PM

A worthy aim but in my case I just wanted an answer to the question I asked. It was clear from the question that I already had one solution and wanted to know if there was a better general way.

0 ·

Answer 1 · 2011-07-13T20:45:26Z

AaronBertrand answered Jul 13, '11

In Denali, yes. Until then, not really. Some examples here: http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/20/sql-server-v-next-denali-metadata-discovery.aspx

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2014-05-02T10:31:04Z

Phil Factor answered May 2, '14

This looks fun. Well, there is a different way, but I'm not sure that it is easier. It is slightly less of a hack. Declare @XMLVersion XML SELECT @XMLVersion=(SELECT TOP 1 * FROM OPENROWSET('SQLNCLI','Server=.\SQL2008;Trusted_Connection=yes;', 'SET NOCOUNT ON;SET FMTONLY OFF;EXEC sys.xp_readerrorlog') for xml raw, elements, xmlschema); WITH XMLNAMESPACES (' http://www.w3.org/2001/XMLSchema' as xsd) SELECT t.value('@name[1]','NVARCHAR(240)') AS [name], replace(Coalesce(t.value('@type[1]','NVARCHAR(50)'), t.value('xsd:simpleType[1]/xsd:restriction[1]/@base[1]','NVARCHAR(50)'), t.value('xsd:complexType[1]/xsd:complexContent[1]/xsd:restriction[1]/@base[1]','NVARCHAR(50)'), 'other'), 'sqltypes:','') AS [type] from @XMLVersion.nodes('/xsd:schema/xsd:element/xsd:complexType/xsd:sequence/xsd:element') x(t) It will actually do any SQL that can be represented in XML. I liked the OPENROWSET idea, though, for doing awkward EXECs!

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2015-12-31T11:54:49Z

azamsohag answered Dec 31, '15

This script will help us to retrieve metadata/column information from a SQL Statement and/or a Table. **Download this script, RETRIVE_METADATA_SCRIPT.sql**. This script will provide the below information .. TABLE_OWNER ORDINAL_POSITION COLUMN_NAME TYPE_NAME LENGTH PRECISION IS_NULLABLE [Read my full post ..][1] [1]: http://nobugfree.blogspot.com/2015/12/sql-script-to-retrieve-metadata-of-sql.html

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question