Recursive Queries in SQL Server 2005
Thursday, September 18, 2008 15:34Posted in category Tips & Tricks
No Comments
among other improvements, SQL Server 2005 included a new XML datatype and enhanced XML functions … thus providing another solution for recursive queries …
for example … if i want to get a list of all of the tables in my database with their associated columns … i might write the following query:
1: SELECT
2: sys.objects.object_id AS ENTITY_ID
3: , sys.objects.type AS ENTITY_TYPE
4: , sys.objects.name AS ENTITY_NAME
5: , sys.columns.column_id AS COLUMN_ID
6: , sys.columns.system_type_id AS COLUMN_TYPE
7: , sys.columns.name AS COLUMN_NAME
8: FROM sys.objects
9: INNER JOIN sys.columns
10: ON sys.objects.object_id = sys.columns.object_id
11: ORDER BY sys.objects.type, sys.objects.name, sys.columns.column_id
however, if i want to return only as many rows as i have tables i would have to rewrite that query to either use a CTE pivot, a complex case statement, or i could take advantage of the new xml functions and xpath:
1: SELECT
2: sys.objects.object_id AS ENTITY_ID
3: , sys.objects.type AS ENTITY_TYPE
4: , sys.objects.name AS ENTITY_NAME
5: , CONVERT(
6: varchar(max), CONVERT(
7: xml, (
8: SELECT
9: CASE
10: WHEN sys.columns.column_id > 1 THEN ','
11: ELSE ''
12: END AS DELIMETER
13: , sys.columns.name AS COLUMN_NAME
14: FROM sys.columns
15: WHERE sys.columns.object_id = sys.objects.object_id
16: ORDER BY sys.columns.column_id
17: FOR XML RAW, ROOT( 'root' ), ELEMENTS XSINIL
18: )
19: ).query('//row/*/text()')
20: ) AS COLUMN_LIST
21: FROM sys.objects
22: ORDER BY sys.objects.type, sys.objects.name
happy programming!
You can follow any responses to this entry through the RSS 2.0 feed.
You can skip to the end and leave a response. Pinging is currently not allowed.