question

Martin 1 avatar image
Martin 1 asked

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-serverssmsmetadata
4 comments
10 |1200

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

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 Likes 2 ·
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 Likes 0 ·
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 Likes 0 ·
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 Likes 0 ·
AaronBertrand avatar image
AaronBertrand answered
10 |1200

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

Phil Factor avatar image
Phil Factor answered
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

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

azamsohag avatar image
azamsohag answered
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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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