Archive

Posts Tagged ‘XQuery’

Query dbproj file to get list of database object

October 3, 2011 Leave a comment

Recently I got a requirement to parse database project to list down all procedures and tables etc.

I binged net for the solution and able to get the query finally.


DECLARE @xml XML;
SELECT @xml = CAST(xmlFile.BulkColumn AS XML)
FROM OPENROWSET(BULK 'D:\WorkArea\Projects\myDatabaseProject.dbproj' ,single_blob) AS xmlFile
IF OBJECT_ID('tempdb..#t') IS NOT NULL
drop table #t

;WITH XMLNAMESPACES( ‘http://schemas.microsoft.com/developer/msbuild/2003’ AS ns)
Select
build.value(‘@Include’,’nvarchar(1000)’) AS _path
into #t
FROM @xml.nodes(‘//ns:Build’) AS R(build)
GO
IF OBJECT_ID(‘tempdb..#Result’) IS NOT NULL
drop table #Result
CREATE table #Result(fname varchar(1000), ftype varchar(100))
— Get Indexes
INSERT INTO #Result (fname, ftype)
SELECT reverse(left(reverse(_path), charindex(‘\’, reverse(_path)) -1)) as _name, ‘index’ from #t WHERE _path LIKE ‘%.index.sql’
UNION ALL
SELECT reverse(left(reverse(_path), charindex(‘\’, reverse(_path)) -1)) as _name, ‘proc’ from #t WHERE _path LIKE ‘%.proc.sql’
UNION ALL
SELECT reverse(left(reverse(_path), charindex(‘\’, reverse(_path)) -1)) as _name, ‘table’ from #t WHERE _path LIKE ‘%.table.sql’
UNION ALL
SELECT reverse(left(reverse(_path), charindex(‘\’, reverse(_path)) -1)) as _name, ‘function’ from #t WHERE _path LIKE ‘%.function.sql’
UNION ALL
SELECT reverse(left(reverse(_path), charindex(‘\’, reverse(_path)) -1)) as _name, ‘view’ from #t WHERE _path LIKE ‘%.view.sql’
UNION ALL
SELECT reverse(left(reverse(_path), charindex(‘\’, reverse(_path)) -1)) as _name, ‘defconst’ from #t WHERE _path LIKE ‘%.defconst.sql’
UNION ALL
SELECT reverse(left(reverse(_path), charindex(‘\’, reverse(_path)) -1)) as _name, ‘schema’ from #t WHERE _path LIKE ‘%.schema.sql’

select * from #Result where ftype = ‘table’

Similarly we can parse SSIS projects also. Script is as shown below.


DECLARE @xml XML;
SELECT @xml = CAST(xmlFile.BulkColumn AS XML)
FROM OPENROWSET(BULK 'D:\WorkArea\MySSISProject.dtproj' ,single_blob) AS xmlFile
IF OBJECT_ID('tempdb..#t') IS NOT NULL
drop table #t
Select
R.build.value('Name[1]','nvarchar(1000)') AS _path
FROM @xml.nodes('//Project/DTSPackages/DtsPackage') AS R(build)

References:
http://sqlblog.com/blogs/jamie_thomson/archive/2011/01/17/querying-visual-studio-project-files.aspx

http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-string-manipulation-workbench/

http://www.simple-talk.com/sql/t-sql-programming/sql-string-user-function-workbench-part-1

Advertisements
Categories: Uncategorized Tags: ,