Querying SSRS Report MetaData for data sources , datasets associated query and connection string information

SSRS  metada    contains wealth    of information which   can be usefull   under varieous circumstances , but extracting    that information can be a timefull process , the basic information  is   stored    under the ReportServer  database  tables, but to retrieve the embedded data  from the report xml  ,the data needs to be   extracted into readable format from an xml format.

The below query  queries the report catalog table of ReportServer  Database   and flattens the xml content   from the Catalog table.

In this below dataset i have  mainly flattened the  Report xml   data   from the Content column of the Catalog table  ,  which was initially converted  to a varbinary type and than to xml,After the conversion to xml and  flattening  the xml , we can associate report name with its related datastes and datasources and connection string     as below



Declare @ReportName varchar(200)
Set @ReportName='SomeReport'
;With Cte
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] AS varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] WHERE type in (2))t 
where t.Name=@ReportName
)
/*** Get the Report Name and DataSource Name ***/,
ReportDataSources
As
(

Select 
dss.dtt.value('(@Name)[1]','varchar(100)') as dsDataSourceName,
Ltrim(Replace(dst.dts.value('(text())[1]','varchar(max)'),'/Data Sources/','')) AS DataSourceName,
Cnts.Cn.value('(text())[1]','varchar(100)') as Cnstring
FROM Cte
cross Apply cte.Content.nodes('//*:DataSource') dss(dtt)
Outer Apply dss.dtt.nodes('.//*:DataSourceReference') dst(dts)
outer Apply Cte.Content.nodes('.//*:ConnectString') Cnts(Cn)
) 
,ReportsAndDS
As
(
SELECT Distinct 
cte.name AS ReportName,

dss.dtt.value('(@Name)[1]','varchar(100)') AS DatasetName,

ds.dstt.value('(text())[1]','varchar(max)') AS DataSourceName,
Ltrim(Replace(shared.Sha.value('(text())[1]','varchar(100)'),'/Datasets/','')) AS SharedDataSet,
COALESCE(ct.cts.value('(text())[1]','varchar(max)'),'ReportQuery') as QueryType,
dst.dts.value('(text())[1]','varchar(max)') AS Query
FROM Cte
cross Apply cte.Content.nodes('//*:DataSet') dss(dtt)
cross Apply dss.dtt.nodes('.//*:CommandText') dst(dts)
Outer Apply dss.dtt.nodes('.//*:CommandType') ct(cts)
Outer Apply dss.dtt.nodes('.//*:DataSourceName') ds(dstt)
Outer Apply cte.content.nodes('.//*:SharedDataSetReference') shared(Sha)

)

,FtReportsAndDs
As
(

Select rs.ReportName,rs.DatasetName,rds.DataSourceName,rs.SharedDataSet ,rs.QueryType ,rs.Query, Substring(rds.Cnstring,CharIndex('=',rds.Cnstring,1)+1, CharIndex(';',rds.Cnstring,1)-(CharIndex('=',rds.Cnstring,1)+1)) as ServerName
from ReportsAndDS rs
Left Join ReportDataSources rds 
on rs.DataSourceName=rds.dsDataSourceName
)
/*** Get the DatSource Name and DatSource XML ***/
,DataSources
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] as varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] where type in (5))t )
,SharedDataSources
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] as varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] where type in (8))t )

,SharedDataSourceConnectionDetails
As
(
Select sdc.Name,dsr.ds.value('(text())[1]','varchar(100)') as DataSourceName ,
ddr.dr.value('(text())[1]','varchar(max)') as Query

from SharedDataSources sdc

Cross Apply sdc.Content.nodes('//*:DataSourceReference') dsr(ds)
Outer Apply sdc.Content.nodes('//*:CommandText') ddr(dr)

)
/*** Prepare the Final Query to Get Report Name and its asscoiated datasources datasets ,QueryType,Query and Connection String ***/

Select t.ReportName,t.DatasetName,t.DataSourceName,t.QueryType,t.Query, t.DatSourceConnectionDetails
,
t.DataSourceCredentials
, Isnull(Substring(DatSourceConnectionDetails,CharIndex('=',DatSourceConnectionDetails,1)+1, CharIndex(';',DatSourceConnectionDetails,1)-(CharIndex('=',DatSourceConnectionDetails,1)+1)),t.DataSourceName) as ServerName
from (
SELECT Distinct 
ds.ReportName,ds.DatasetName,IsNull(ds.DataSourceName,ds.ServerName) as DataSourceName,ds.Query,ds.QueryType,

dss.dtt.value('(./*:ConnectString)[1]','varchar(100)') AS DatSourceConnectionDetails,
dss.dtt.value('(./*:CredentialRetrieval)[1]','varchar(100)') AS DataSourceCredentials

FROM FtReportsAndDs ds Left join DataSources ctee ON
ds.DataSourceName=ctee.Name
Outer Apply ctee.Content.nodes('//*:DataSourceDefinition') dss(dtt)
UNION ALL
SELECT Distinct 
ds.ReportName,shd.Name as datasetName,shd.DataSourceName,shd.Query,ds.QueryType,
dss.dtt.value('(./*:ConnectString)[1]','varchar(100)') AS DatSourceConnectionDetails,
dss.dtt.value('(./*:CredentialRetrieval)[1]','varchar(100)') AS DataSourceCredentials
FROM ReportsAndDS ds Inner Join SharedDataSourceConnectionDetails shd
on ds.SharedDataSet=shd.Name
Left Join 
DataSources ctee ON
ctee.Name=shd.DataSourceName
Outer Apply ctee.Content.nodes('//*:DataSourceDefinition') dss(dtt))t

This is the snapshot of the above query without any filter applied for report  for this demo.

As you can see i haveflattened the xml content so that the we can see each report its associated dataset datasource name and its connection string, this can be very handy when we have very large number of reports   and wanted to migrate those reports to see the depended data sources   .

Splitting Strings in TSql using CTE

There are number of occasions where we need to split a string to individual values,one such common use  case is for multivalue parameters  in ssrs, when a stored procedure  is used  to  feed the dataset   we need to split the    string parameter  values  .


This can be implemented in a number of ways, i chooose to use a cte  and than embedd that cte in a table valued function,   and this use case  only  works  in sql server 2012 , for other versions, replace the  conditional IIF function with a case


Consider  u   got string values of countries as shown below

Declare @sp VarChar(max)='UK,USA,FRANCE,CANADA,MEXICO,BRAZIL,CHINA, MALAYSIA,TURKEY,INDIA,AUSTRALIA'


and we want to split it into individual countries ,to split the string, whatever logic we use, we need to know the  postion of  individual string  i.e the starting position and ending position of the sorrounded delimiters .

This is where recursive cte  can be very usefull,the way it is done is we use Charecter index   string function in the anchor query to get the first  end position of a delimiter   of first individual string   and 0 as the starting position, as there will be no delimeters at the start of string.

Than we use these values recursivly to get the  next delimeter position ,and to do that we  increment the previous  delimeter postion in the charecter index function start value,  in this case ,after the anchor query gets 3 as the first delimeter value  than it is incremented by 1 i.e   4 and this 4 will become the string starting  position for the second individual string i.e USA  and the charecter index gets the  next individual ending  delimeter position as 7 ,and this logic is repeated  recursively to build the starting and ending delimeter position for each string as shown in the second figure.


;With Cte
As
(

Select   CHARINDEX(',',@sp,0)  tt, cast (0  as int) t
Union ALL
Select  CHARINDEX(',',@sp,tt+1),  cast(tt as int) as t      from  Cte
where  tt>0
)

Select    SUBSTRING(@sp,IIF(t=0,1,t+1),IIF(tt=0,len(@sp)-t,tt-t-1) )  from Cte

The above cte gets the delimited index values  recursivly    for each individual   string    as shown below

Once we  have these values ,the next statement
Select    SUBSTRING(@sp,IIF(t=0,1,t+1),IIF(tt=0,len(@sp)-t,tt-t-1) )  from Cte

simply selects the values between those delimeters
as shown below

 we can embedd this logic into a table valued function as shown below and subsequently use this function in the stored procedure  as shown in the query following this function


 Create   Function apn_split(@Sp varchar(max))

 Returns Table

 As
 Return
WIth Cte
as
(

Select   CHARINDEX(',',@sp,0)  tt, cast (0  as int) t
Union ALL
Select  CHARINDEX(',',@sp,tt+1),  cast(tt as int) as t      from  Cte
where  tt>0
)

Select    SUBSTRING(@sp,iif(t=0,1,t+1),iif(tt=0,len(@sp)-t,tt-t-1) )  as Country  from Cte



Consider to build to report to analyse sales data (in this example  i'm using AdwentureWorksDW2008R2) which will  allow the end user to  select multiple countries    as show below

Than you can use the above function in the stored procedure to pass  multiple values from ssrs to stored procedure  as shown below

Alter Procedure ap_ProductSalesByCountry

@Country varchar(max)
As
SELECT     DimProduct.EnglishProductName, DimProductSubcategory.EnglishProductSubcategoryName, DimProductCategory.EnglishProductCategoryName,
                      DimSalesTerritory.SalesTerritoryCountry, DimDate.EnglishMonthName, DimDate.CalendarYear, FactResellerSales.SalesAmount
FROM         FactResellerSales INNER JOIN
                      DimSalesTerritory ON FactResellerSales.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey INNER JOIN
                      DimProduct ON FactResellerSales.ProductKey = DimProduct.ProductKey INNER JOIN
                      DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
                      DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
                      DimDate ON FactResellerSales.OrderDateKey = DimDate.DateKey
WHERE     DimSalesTerritory.SalesTerritoryCountry  in ( Select distinct country    from apn_split(@Country))

SSRS Parameter Post backs

Most people consider parameter postbacks in SSRS as  an  unwanted operation, but some times they are necessary ,I had  a situation where i need to post back when any of the parameters is selected 

In this situtaion ,create  a   new parameter at the bottom of all parameters   and set some value like datetime as default ,one important  thing to notice is that , when  you set this  default parameter sourced from a dataset query , than this will not work,it needs to  be sourced manually  as shown below.

Irrespective of what you  select in advance parameters (as always refresh or never refresh), setting the default value as above ,will always result in post back of all  the parameters   above   this parameter ,as long as this parameter  is the  last parameter .

Replacing Selfjoins with analytic functions in Sql Server 2012

Sql Server 2012 has introduced a  set of new  analytic  functions ,which helps to  get  different analytical   answers   from an dataset , with  a less complex and more understandable  approach.

The aim of this article is to implement newly introduced lag function ,and the same implementation with an old approach, in my case a self join   based.


DatasetUsed:Adventure Works2008R2

Imagine I want  tocompare  the sales in each country by  current  calendar year  with the sales for  previous calendar year for the below dataset


with cte
as
(
select  d.CalendarYear,t.SalesTerritoryCountry ,sum(s.SalesAmount) as sales  from [dbo].[DimDate] d inner join [dbo].[FactResellerSales] s
on d.DateKey=s.OrderDateKey
inner join DimSalesTerritory t
on s.SalesTerritoryKey=t.SalesTerritoryKey
group by d.CalendarYear,t.SalesTerritoryCountry)


Implementing using the lag function,

select cte.CalendarYear,cte.SalesTerritoryCountry,cte.sales as  CurrentYearSales,lag(cte.sales,1,0) over(partition by cte.salesterritorycountry order by Cte.calendaryear) as t   from cte


Implementing using a selfjoin approach

select cte.CalendarYear,cte.SalesTerritoryCountry,cte.sales as CurrentYearSales,Coalesce(t.sales,0) as PrevYearSales from cte
left join cte  as t on
t.CalendarYear=cte.CalendarYear-1
and  cte.SalesTerritoryCountry=t.SalesTerritoryCountry
order by SalesTerritoryCountry,calendaryear

Note:The null values which were replaced by coalsce in this query, is done by the third attribute of lag function with a value 0(lag(cte.sales,1,0))


As you can observe analytic functions makes  the code more readable,  and we can embed more complex logic dervived from this.

Sql Server 2012 Always on Availability Groups

Sql Server 2012 Always on Availability group is a new form of   High availability  which has many advantages  to the database  mirroring  and it can be  seen as an alternative solution  to the database  mirroring solution  for  high availability

Some of the important advantages of Always On Groups

• Always on availability group enables  group of data bases to fail over  when compared to single database  of mirroring
• It allows up to 4 secondary  replicas  which  can be used as an active readable database and fail over over occurs on a per replica bases. 
• Supports  automatic fail over , automatic page repair  and the fail over occurs at the instance level

Setting up Always on Availability group

Key Steps:

• Sql server Always on requires all the participating nodes to be joined to a failover cluster
• It doesn't require sql server to be installed as a clustered instance
• Use the domain accounts for the sql server service accounts of the all the participating instances as it enables to create mirroring end points of the all the participating nodes automatically 
• Always On requires the primary replica  database to be in full recovery mode and  if you got the secondary instances with different names than  you need to manually take the full backs and transaction log backups and leave it in a recovery state

Creating the Failover Cluster

Enable the fail over  future on each participating  node  from the add futures section of  server configuration dashboard as shown below

• The next step is to create fail-over cluster   which can be done by  going to fail over cluster manager from the start administrative tools  and when on the cluster manager click create new cluster as shown below

• In the next step   add the participating nodes  by browsing through AD directory as shown below

• After joining the nodes to cluster, enable always on from the sql server configuration manager and restart the sql server service as shown below

Creating the AlwaysonGroup

• Go to any one  sql server instance which you want make  as a primary replica  by going to sql server management studio  and right clicking always on management  and new availability group  as shown below

• Click next as shown below

• Click next  give a name

• In the next step select a data base  to be part of availability group , as i have already setup the always on group previously it shows  as already part of the availability group but you can select any  user database  which is in full recovery mode 

In the next step u have the option to  configure the  mode of the availability group as in mirroring you can choose high performance asynchronous mode or highly available synchronous mode,in the same section u need to add the secondary replicas ,  and also the option to configure  how  the secondary replicas to be configured you got three options read intent only ,yes, no,read intent only allows for read only connections and if you select yes it allows all connections if you set no its doesn't allow any connections  as shown below

• In the next step you can configure whether you want the databases  should be taken backup and applied automatically at the secondary replicas or whether you want to join to the already existing databases  which already have been backed up and restored manually in a recovering state i have chosen the second option to join as my secondary  instances are on different locations from the primary instance   as shown below

• Click next to perform the validations as shown below

Click next to verify the configuration choices you made as shown below and later click next to finish 

Sql Server Open Rowset

Another  future of Sql Server a part from linked server to access data from another instance of sql server  is to use the OpenRowset function  ,this future allows for accessing data from another instance of sql server  , but requires  you to specify the connection details every time when you want to use to .

For accessing the data using OpenRowset function sql server needs to configure advanced options and than enable adhoc distributed queries as shown below

it is important to note that, there should be only  single space between each word of  Ad Hoc Distributed Queries  or else the query will fail

Configuring Linked Server in SQl Server 2012

Accessing remote data is  one of the common  tasks  when working on a data related job in sqlserver,linked server is a future in sql server which enables to add the remote sql servers to a local server and makes easy  easy to use a user defined server with  the four part naming convention to query data .

To set set linked server you need to go to server objects  and linked server as shown below

The next step is to right click the linked server and create a new linked server
and fill the fields as necessary  as shown below

As shown below you need to select

1. Other Data Source
2. Provider=SqlNative Client(Sql Server)
3. ProductName= SqlServer
4. DataSource=Name of the remote instance

other fields are optional

In the next step you need to provide the security context for this server,my servers are on the same domain so i have selected  the login under current user context but u can also give remote login details on the final optionas be made under a different security context

SQL Server Procedure Cache and Execution Plans

 Procedure cache and data  cache information  in sql server can play a vital role in understanding the performance of the sql server.These two contains  two core aspects of  sql server performance  namely the query  executions plans and the  data   pages  both   of which are stored in buffer cache

Procedure Cache:
Sql server engine  generates  query execution plans  by taking different factors into consideration such as  existing indexes , tables, joins  etc  and at the end of this process  it produces an execution plan, which it considers as  best for executing the query .This entire process is done by a cost based  allocation approach  in which  it  estimates   percentage  of   cost  of resources  to  process each operation in the query and generates an best possible execution plan to execute the query .

Each time  Sql Server  generates an execution plan  it  consumes certain amount of system resources    and to avoid  generating a  new  execution plan  for  same query which has been executed previously   sql server  has a mechanism called  procedure cache ,which  stores these execution plans  in memory pool , so that when  a query is executed  and an existing plan is available in procedure cache, it will use that plan  and helps to save the system resources .The amount of time this query is stored in the procedure cache  is again another process, in which sql server  uses a age based approach ,which essentially means if a plan is re used again , than  it will  keep the execution plan  in the procedure cache   and the plan will live  for longer time in the procedure cache, and if a plan is not used ,than the plan  will be eventually  removed from the procedure cache  as other executions plans get added  and by doing this,  it uses buffer cache efficiently .

Understanding Query execution plan is very important to query design and indexing ,sql server management studio enables to view estimated execution plan and actual execution plan as shown below

The execution plan in the above diagram  contains number of operators, to show what actually is happening  when sql server is trying to build the execution plan for the query  .Each operator has a associated cost with it , like in this case the cost of clustered index scan  is 100% as  shown  in the above diagram ,I will change the query slightly  and we will observer how the new execution plan looks like

In this  second query as shown  in the below diagram  i have added a  filter  on geography key and the execution plan  changed  considerable   as shown as below

As you can see when i added a filter , the execution plan changed  and now there is a index seek operation due to the fact that there is a non clustered index on the geography id filter column   and naturally the execution plan determined that the  index  seek is is more cost efficient operation due to the fact fact that it fetches few records when compared to a index scan and also you can see ,there is a  Key Look Up  operator in the execution plan which is essentially used to look up the records from the non clustered index to the clustered index and has an associated cost of 50%  and also  there is a Nested loop join which is the result of  key loop operator.Explaining  each operator in execution plan is beyond the scope of this post  and the above examples were used to show, what is an execution plan, and how it is relevant  in query building



Usefull   DMV   to get  Procedure  Cache   information in Sql Server

select s.usecounts as execcounts , s.size_in_bytes,s.cacheobjtype,  t.text ,db_name(st.database_id) as DatabaseName,st.total_elapsed_time   from sys.dm_exec_cached_plans  s
inner join  sys.dm_exec_procedure_stats  st on  s.plan_handle=st.plan_handle
cross apply sys.dm_exec_sql_text(s.plan_handle) t


UseFull  DBCC command to clear Procedure cache in sql server

DBCC FREEPROCCACHE

Sql Server Query for Blocked Session and login names

The below query can be used to find out the login names and session id of the blocked users,the detailed explanation of blocking is beyond the scope of the post  and this post covers only to extract blocking information
...................................................................................................................................................

To replicate the blocking, i have opened a transaction record  for  an  update  by a user    and   another user is querying the same record  ,both users are using  the sql server default  isolation of read committed  and this causes the blocking,  as the second  user is waiting to acquire a shared lock on the same record

I have made the screen shots at the end of query  and highlighted   activity  monitor  screen shot  to show     the query and blocked session
-----------------------------------------------------------------------------------------------------

SELECT  distinct  case when r.session_id=s.session_id then s.nt_user_name end as BlockedLogin ,r.session_id as BlockedSessionId ,r.status ,
 case when r.blocking_session_id=st.session_id then st.nt_user_name end as BlockedByLogin, r.blocking_session_id as BlockedBySessionId,s.nt_user_name
    ,wait_type ,wait_time ,wait_resource
    ,transaction_id
FROM sys.dm_exec_requests r  inner join  sys.dm_exec_sessions s
on r.session_id=s.session_id inner join sys.dm_exec_sessions st
on r.blocking_session_id=st.session_id
  WHERE r.status = N'suspended';
GO

additionally to get the blocked query  text you can use the below query



SELECT  distinct  case when r.session_id=s.session_id then s.nt_user_name end as BlockedLogin ,r.session_id as BlockedSessionId ,r.status ,
 case when r.blocking_session_id=st.session_id then st.nt_user_name end as BlockedByLogin, r.blocking_session_id as BlockedBySessionId,s.nt_user_name
    ,wait_type ,wait_time ,wait_resource,qry.Text as BlockedQuery
    ,transaction_id
FROM sys.dm_exec_requests r  inner join  sys.dm_exec_sessions s
on r.session_id=s.session_id inner join sys.dm_exec_sessions st
on r.blocking_session_id=st.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)  Qry
  WHERE r.status = N'suspended';
GO

The result can be seen below

The Same can be observed on activity monitor as below

As you  can observe we can use combination of many tools in sql server diagnosis 

Average Cpu Time by Top 10 Queries

SELECT TOP 10 total_worker_time/execution_count AS [Avg CPU Time],
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

Query for Sql Server IO Utilization by each database

....................................................................... Io Utilization by each databse in MB ......................................................................... Note:Use of these queries are up to the  user  responsibilities,so i do not recommend to use these views without  testing on a test server,  as the impact of these views on system resources  can be intense at times, and i do not own these views  , the total credit goes to the individual contributors  and companies , the purpose of these posting  is to systematically bring different views at one place for troubleshooting sql server ......................................................................... SELECT

DB_NAME(database_id) AS [Database Name],

CAST(SUM(num_of_bytes_read + num_of_bytes_written)/1048576 ASDECIMAL(12, 2)) AS io_in_mb

FROM

sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]

GROUP BY database_id;

The above query show  the io utilization by each database as shown below

Usefull Queries for Sql Server data base Monitoring

I have collected a list of useful queries  for database  Monitoring ,  for the purpose of these posts,   i'm   running  few queries on adventure works   databases  , i will be using these views to demonstrate the impact of those queries   .

Note:Use of these queries are up to the  user  responsibilities,so i do not recommend to use these views without  testing on a test server,  as the impact of these views on system resources  can be intense at  times, and i do not own some of  these views and for those views  total credit goes to the individual contributors  and companies , the purpose of these posting  is to systematically bring different views at one place for troubleshooting  in sql server
.--------------------------------------------------------------------------------
Index Fragmentation  in Sql Server

---------------------------------------------------------------------------------

IF EXISTS ( SELECT *

    FROM [tempdb].[dbo].[sysobjects]

    WHERE id = OBJECT_ID(N'[tempdb].[dbo].[tmp_indexfragmentation_details]'))

    DROP TABLE [tempdb].[dbo].[tmp_indexfragmentation_details]

GO



CREATE TABLE [tempdb].[dbo].[tmp_indexfragmentation_details](

    [DatabaseName]                  [nvarchar] (100) NULL,

    [ObjectName]                    [nvarchar] (100) NULL,

    [Index_id]                      INT,

    [indexName]                     [nvarchar] (100) NULL,

    [avg_fragmentation_percent]     float NULL,

    [IndexType]                     [nvarchar] (100) NULL,

    [Action_Required]               [nvarchar] (100) default 'NA'

) ON [PRIMARY]



DECLARE @dbname varchar(1000)

DECLARE @sqlQuery nvarchar(4000)



DECLARE dbcursor CURSOR for

SELECT name FROM sys.databases WHERE name NOT IN ('master', 'tempdb','model', 'msdb')



OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @dbname



WHILE @@FETCH_STATUS = 0

BEGIN

    SET @sqlQuery = '

    USE [' + @dbname + '];



    IF EXISTS

    (

        SELECT compatibility_level

       FROM sys.databases

        WHERE

            name  = N'''+ @dbname +'''

            AND compatibility_level >= 90

    )

    BEGIN

        INSERT INTO [tempdb].[dbo].[tmp_indexfragmentation_details]

        (

            DatabaseName

            , ObjectName

            , Index_id

            , indexName

            , avg_fragmentation_percent

            , IndexType

        )

        SELECT

            db_name() as DatabaseName

            , OBJECT_NAME (a.object_id) as ObjectName

            , a.index_id, b.name as IndexName

            , avg_fragmentation_in_percent

            , index_type_desc

        FROM

            sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

            JOIN sys.indexes AS b

        ON

            a.object_id = b.object_id

            AND a.index_id = b.index_id

        WHERE

            b.index_id <> 0

            AND avg_fragmentation_in_percent <> 0

    END;'

   

    EXEC sp_executesql @sqlQuery

   

FETCH NEXT FROM dbcursor

INTO @dbname

END



CLOSE dbcursor

Deallocate dbcursor




UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]

SET Action_Required = 'Rebuild'

WHERE avg_fragmentation_percent >30

GO



UPDATE [tempdb].[dbo].[tmp_indexfragmentation_details]

SET Action_Required = 'Reorganize'

WHERE avg_fragmentation_percent <30 and avg_fragmentation_percent >5

GO



SELECT * FROM [tempdb].[dbo].[tmp_indexfragmentation_details]

ORDER BY databasename

This  view is great  in the sense that it gives a clear view of what to do  and on which object the impact will be ,as you can see fragmentation  anything over 30%  an   index rebuild is specified  and fragmentation between  30% to to %5 a index reorganization  is  specified