Parsing Stored Procedure Result Set

One thing I've always wanted to be able to do is to parse the result set(s) of a stored procedure, so that I can easily create a structure to contain it's output. This thread on sqlteam.com details a way to capture the output without knowing the structure ahead of time. This is done by using a linked server that loops back to itself, and then runs this statement:

select * into #t from openquery(loopback, 'exec yourSproc')

to select the results into a temp table, the metadata of which can then be parsed as needed. This leads to the 'pie in the sky' idea of a stored proc that, given the name of another stored proc, will output a CREATE TABLE script that mirrors the result set of the second stored proc.

Removing Trailing Zeroes

I recently encountered the classic problem of removing trailing zeroes from formatted numeric output, and after mocking up some complicated code and doing some research, I came across a thread that says to convert it to float before varchar. It's a perfect solution that is simple and works correctly.

View EPs Neatly

Here's some code to view Extended Properties of a table in a neat denormalized report:

-- replace 'TABLENAME' with the name of your table:
DECLARE @ObjId int; SET @ObjId = OBJECT_ID('TABLENAME')

DECLARE @sql varchar(max)
SET @sql =
'SELECT
TableName = T.Name
,ColumnName = C.Name
'

; WITH TableNameBase AS (
SELECT *
FROM SYS.TABLES
WHERE OBJECT_ID = @ObjId
), PropNames0 AS (
SELECT DISTINCT Name
FROM SYS.EXTENDED_PROPERTIES
WHERE Major_Id = (SELECT [OBJECT_ID] FROM TableNameBase)
AND Minor_Id > 0
), PropNames AS (
SELECT Name, NameOrder = ROW_NUMBER() OVER (ORDER BY Name)
FROM PropNames0
)
SELECT * INTO #PropNames FROM PropNames

SELECT @sql = @sql +
' ,[' + P.Name + '] = ISNULL(P' + LTRIM(STR(P.NameOrder)) + '.Value, '''')
'
FROM #PropNames P

SET @sql = @sql +
'FROM SYS.TABLES T
JOIN SYS.COLUMNS C
ON C.OBJECT_ID = T.OBJECT_ID
'

SELECT @sql = @sql +
'LEFT JOIN SYS.EXTENDED_PROPERTIES P' + LTRIM(STR(P.NameOrder)) + '
ON P' + LTRIM(STR(P.NameOrder)) + '.Major_Id = T.OBJECT_ID
AND P' + LTRIM(STR(P.NameOrder)) + '.Minor_Id = C.Column_Id
AND P' + LTRIM(STR(P.NameOrder)) + '.Name = ''' + P.Name + '''
'
FROM #PropNames P

SET @sql = @sql +
'WHERE T.OBJECT_ID = ' + LTRIM(STR(@ObjId))

PRINT @sql
EXEC(@sql)

DROP TABLE #PropNames

Query to List Tables and their Primary Keys

This query produces a resultset of two columns: every table in the current database, and the corresponding primary key expression if one exists:

; WITH Base AS (
SELECT
TABLE_NAME = QUOTENAME(T.TABLE_SCHEMA) + '.' + QUOTENAME(T.TABLE_NAME)
,C.Column_Name
,C.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLES T
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON PK.TABLE_NAME = T.TABLE_NAME
AND PK.TABLE_SCHEMA = T.TABLE_SCHEMA
AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C
ON C.CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND C.TABLE_NAME = PK.TABLE_NAME
AND C.TABLE_SCHEMA = C.TABLE_SCHEMA
), OrderIt AS (
SELECT
TABLE_NAME
,Column_Name = CONVERT(varchar(max), Column_Name)
,ORDINAL_POSITION
,RecurseVar = ROW_NUMBER() OVER (PARTITION BY TABLE_NAME ORDER BY ORDINAL_POSITION)
FROM Base
), Recursed AS (
SELECT
Table_Name
,Column_Name
,RecurseVar
FROM OrderIt
WHERE RecurseVar = 1
UNION ALL
SELECT
B.Table_Name
,Column_Name = RTRIM(R.Column_Name) + ', ' + B.Column_Name
,B.RecurseVar
FROM Recursed R
JOIN OrderIt B
ON B.Table_Name = R.Table_Name
AND B.RecurseVar = R.RecurseVar + 1
), GetMax AS (
SELECT
Table_Name
,MAX_RecurseVar = MAX(RecurseVar)
FROM Recursed
GROUP BY Table_Name
), Results AS (
SELECT
R.Table_Name
,Primary_Key = R.Column_Name
FROM Recursed R
JOIN GetMax G
ON G.Table_Name = R.Table_Name
AND G.MAX_RecurseVar = R.RecurseVar
)
SELECT * FROM Results
ORDER BY Table_Name

XML SQL - Probing Depth of Document

Now let's say that you want to know how many levels deep this relationship goes. Without knowing the answer ahead of time, we have to use a recursive query to determine this:

; WITH XML_Doc AS (
SELECT
id
,parentid
,[level] = 1
,nodetype
,localname
,prev
,text
FROM #tmp2
WHERE LocalName = 'RootNode'
UNION ALL
SELECT
T.id
,T.parentid
,[level] = R.[level] + 1
,T.nodetype
,T.localname
,T.prev
,T.text
FROM XML_Doc R
JOIN #tmp2 T
ON R.Id = T.ParentId
)
SELECT *
INTO #XML_Doc
FROM XML_Doc

XML Parsing with Recursive Table Structure

After you load the XML data into the temp table using the code from yesterday's post, you end up with a hierarchical structure representing the XML data with simple id/parentid columns. So a node at the top level with an id=1 would have nodes at the next level with parentid=1, where id is unique and parentid always references id. There is also a column called 'nodetype' that seems to determine what this node represents. Now just on inspection, I deduced that nodetype=1 are root nodes, 3's are leaf nodes, and 2's are a combination (they have attribute info and children). This code will condense type 3's into their parent:

SELECT
X1.Id
,X1.ParentId
,X1.NodeType
,X1.LocalName
,X1.prev
,text = COALESCE(X1.text, X3.text)
INTO #tmp2
FROM #tmp X1
LEFT JOIN #tmp X3
ON X3.ParentId = X1.Id
AND X3.NodeType = 3
WHERE X1.NodeType <> 3
ORDER BY Id

XML into SQL

I'm working on loading an XML file into SQL right now, and struggling a bit with it. Not so much the coding itself, but figuring out how to represent the hierarchical XML data in a relational SQL table.

I did discover this tidbit for taking advantage of SQL's built-in tools for handling XML:

DECLARE @hdoc int DECLARE @doc varchar(max) SELECT @doc = CONVERT(varchar(max), XML_Column) FROM dbo.XML_Table EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc PRINT @hdoc SELECT * INTO #tmp FROM OPENXML (@hdoc, '/RootNodeName',2) EXEC sp_xml_removedocument @hdoc

Works well.

Getting the Real Name of a Temp Table

If you've ever wanted to retrieve the schema for a temp table, you probably noticed that in the TempDb database, the name of your temp table is not quite the same (unless you're using a global temp table - this entry does not apply to those). Say that you create a temp table named "#tmp" in your development database. If you go into TempDb, for example in the Sys.Tables view, you will find your table name, but padded on the right with underscores and a string of digits, to a length of 128 characters. So if you try to look up the schema in TempDb.Information_Schema.Columns using "#tmp", it will fail. To alleviate this problem, I wrote a simple SELECT that returns the true name of that temp table as it is stored in TempDb:

SELECT TempTableName = OBJECT_NAME(OBJECT_ID('TempDb..#tmp'), (SELECT Database_Id FROM SYS.DATABASES WHERE Name = 'TempDb'))