Find Procedures for Reports using Query


--WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
--SELECT ReportName = name
--    ,DataSourceName = x.value('(@Name)[1]', 'VARCHAR(250)')
--  ,DataProvider = x.value('(ConnectionProperties/DataProvider)[1]','VARCHAR(250)')
--  ,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
-- FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
--      FROM ReportServer.dbo.Catalog C
--     WHERE C.Content is not null
--      AND C.Type = 2

-- ) a
-- CROSS APPLY reportXML.nodes('/Report/DataSources/DataSource') r ( x )
--ORDER BY name ;


WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
SELECT ReportName = name
   -- ,DataSetName = x.value('(@Name)[1]', 'VARCHAR(250)')
 --,DataSourceName = x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
 ,CommandText = x.value('(Query/CommandText)[1]','VARCHAR(250)')
 --,Fields = df.value('(@Name)[1]','VARCHAR(250)')
 --,DataField = df.value('(DataField)[1]','VARCHAR(250)')
 --,DataType = df.value('(rd:TypeName)[1]','VARCHAR(250)')
 --,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
 FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
      FROM ReportServer.dbo.Catalog C
     WHERE C.Content is not null
      AND C.Type = 2
 ) a
 CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
 --CROSS APPLY x.nodes('Fields/Field') f(df)
ORDER BY name

Comments

Post a Comment

Popular posts from this blog

TSQL To Get All the Names of tables and Databases whose data have changed in last 24 Hours

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/2/2017 -- Description: To Get All the Names of tables and Databases whose data have changed in last 24 Hours -- ============================================= ALTER PROC UP_FindAllDatabaseHaveChanges AS     BEGIN         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             (               ID INT IDENTITY(1, 1)                      PRIMARY KEY ,       ...
Read more

To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/3/2017 -- Description: To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases -- ============================================= Alter PROC UP_FindAndBackupAllDatabaseHaveChanges AS     BEGIN ----**************************START: To get all the tables and database names whose data have changed in last 24 hours **************************----         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             ( ...
Read more

Apply paging in procedure

@intPageId int, @intPageSize int select * from ( Select     row_number() over (order by  TableA.intID asc ) r ,     TableA.intID,tableA.hName,TableB.Address  from TableA   Join TableB on TableA.intid = TableB.intId   ) A   where A.r  between ( (@intpageid-1)*@intpagesize)+1 and ((@intpageid-1)*@intpagesize)+@intpagesize The query in bold text is for paging, and the normal text query is that which returns your records. OR SELECT * from PressRelease  order by CreatedDate desc OFFSET (@PageID-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
Read more