Category Archives: TSQL Scripts

how to get length\size of each row in table

CREATE PROCEDURE USP_GET_LENGTH_EACH_ROW  @TABLE_NAME VARCHAR(100) = NULL
/*  
--##  
AUTHOR - RAKESH SHARMA  
Ver.1.0  
DESCRIPTION - PROCEDURE WILL LOOP THROUGH EACH OBJECT AND CALCULATE THE SIZE OF EACH ROW  
***CAUTION - DO NOT RUN THIS ON PRODUCTION AS THIS MIGHT TAKE HOURS DEPENDING ON THE NUMBER OF ROWS AND THIS IS NOT RECOMENDED TOO  
***THERE ARE SEVRAL OTHER WAYS TO IDENTIFY THE FREE SPACE IN THE TABLE AND DATA FILES**  
INSTRUCTIONS:- HOW TO EXECUTE
	EXEC USP_GET_SIZE_EACH_ROW_1 (NO PARAMETER)
	DEFAULT PARAMETER =NULL --> WHICH MEANS THIS WILL LOOP THORUGH ALL THE TABLES AND CHECK SIZE OF EACH ROW
    
	PASSING TABLENAME TO GET THE SIZE OF EACH ROW
	EXEC USP_GET_SIZE_EACH_ROW_1 'TEST_COLUMN_STORE_INDEX'
*/  
AS  
SET NOCOUNT ON  
BEGIN  
DECLARE @table nvarchar(128)  
DECLARE @idcol int  
DECLARE @sql nvarchar(max)  
SET @idcol = 1  
DECLARE @COLUMN VARCHAR(200)  
IF @TABLE_NAME IS NULL
		BEGIN
						DECLARE emp_cursor CURSOR FOR       
						SELECT NAME  from SYS.SYSOBJECTS   WHERE TYPE='U'  
						OPEN emp_cursor      
						FETCH NEXT FROM emp_cursor       
						INTO @table      
						WHILE @@FETCH_STATUS = 0      
						BEGIN      
						SET @COLUMN=(SELECT TOP 1 COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@table)  
						PRINT @TABLE  
						PRINT @COLUMN  
						set @sql = 'select ' + 'ROW_NUMBER() OVER (ORDER BY ' + @COLUMN +') AS ROWNUM' +' , (0'  
							select @sql = @sql + ' + isnull(datalength([' + name + ']), 1)'   
									from sys.columns where object_id = object_id(@table)  
							set @sql = @sql + ') as ROWSIZE from ' + @table + ''  
						 EXEC (@sql)  
							FETCH NEXT FROM emp_cursor       
						INTO @table      
						END       
						CLOSE emp_cursor;      
						DEALLOCATE emp_cursor;      
			END
			ELSE
			BEGIN
						PRINT @TABLE_NAME
						SET @COLUMN=(SELECT TOP 1 COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@TABLE_NAME)  
						PRINT @TABLE_NAME  
						PRINT @COLUMN  
						
						set @sql = 'select ' + 'ROW_NUMBER() OVER (ORDER BY ' + @COLUMN +') AS ROWNUM' +' , (0'  
							select @sql = @sql + ' + isnull(datalength([' + name + ']), 1)'   
									from sys.columns where object_id = object_id(@TABLE_NAME)  
							set @sql = @sql + ') as ROWSIZE from ' + @TABLE_NAME + ''  
						 EXEC (@sql)  
			END
END  

  

how to get table and avg. rows size in sql server

ALTER PROCEDURE USP_GET_TABLE_SIZE_AVG_ROW_SIZE  
/*
DESCRIPTION - PROCEDURE WILL CALCULATE AVG SIZE OF ROW SIZE  
***CAUTION - DO NOT RUN THIS ON PRODUCTION AS THIS MIGHT TAKE HOURS DEPENDING ON THE NUMBER OF ROWS AND THIS IS NOT RECOMENDED TOO  
***THERE ARE SEVRAL OTHER WAYS TO IDENTIFY THE FREE SPACE IN THE TABLE AND DATA FILES**  
***NOTE:- TEST THIS SCRIPT ON TEST MACHINE FIRST MODIFY ACCORDINGLY BEFORE EXECUTING ON PRODUCTION
*/  
AS  
SET NOCOUNT ON  
BEGIN  

IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL  
 DROP TABLE #SpaceUsed  
  
CREATE TABLE #SpaceUsed (  
  TableName sysname  
 ,NumRows BIGINT  
 ,ReservedSpace VARCHAR(50)  
 ,DataSpace VARCHAR(50)  
 ,IndexSize VARCHAR(50)  
 ,UnusedSpace VARCHAR(50)  
 )   
  
DECLARE @str VARCHAR(500)  
SET @str =  'exec sp_spaceused ''?'''  
INSERT INTO #SpaceUsed   
EXEC sp_msforeachtable @command1=@str  
  
  
DECLARE @old_tname VARCHAR(100)  
DECLARE @avg_row INT  
DECLARE @tname VARCHAR(100)  
DECLARE @data INT  
DECLARE @row INT  
CREATE TABLE #size (tname VARCHAR(1024),  
row INT,  
reserve CHAR(18),  
data CHAR(18),  
index_size CHAR(18),  
unused CHAR(18))  
CREATE TABLE #results (tname VARCHAR(100),  
data_size INT,  
rows INT)  
SELECT TOP 1 @tname=name FROM sysobjects where xtype = 'u' ORDER BY NAME  
SET @old_tname = ''  
WHILE @old_tname < @tname  
BEGIN  
INSERT INTO #size exec ('sp_spaceused ' + @tname + ',true')  
SELECT @row=row, @data=substring(data,1,CHARindex(' ',data)-1) from #size  
INSERT INTO #results values (@tname,@data,@row)  
SET @old_tname = @tname  
SELECT top 1 @tname=name from sysobjects where xtype = 'u' and name > @old_tname order by name  
end  
  
SELECT TableName, NumRows,   
CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,  
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,  
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,  
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB  
FROM #SpaceUsed  
ORDER BY ReservedSpace_MB desc  
  
  
SELECT UPPER(left(tname, 100)) AS TABLE_NAME,  
rows AS TOTAL_ROWS, (DATA_SIZE/1024) DATA_SIZE_MB,  
CASE rows WHEN 0 THEN 0  
ELSE (1024*cast(data_size as float))/cast(rows as float)  
END as AVG_ROW_SIZE_KB,  
CASE rows WHEN 0 THEN 0  
ELSE 8024.0/((1024*cast(data_size as float))/cast(rows as float))  
END as NUMBER_ROWS_PER_PAGE  
FROM #results  
DROP TABLE #results  
DROP TABLE #size  
END  
  

SQLCodeBlock

SQLCodeBlock is a small tool that contains all day today scripts that database administrators uses for troubleshooting and normal SQL Server health checks. Its a small initiative to collaborate all scripts and assembled them into a windows based application.

You can download and install this tool on you local machine and run it any time, its very easy to use, just open this tool select category,select sub-category and then click fetch script and then click copy to clipboard. CodeBlock2CodeBlock3CodeBlock4

>>SQLCodeBlack Download<< One Drive

>>SQLCodeBlack Download<< Google Drive

Download the zip file and extract the files and click setup.exe to install it on your machine

How to read and parse rsreportserver.config file for Report Server in SSRS



 

DECLARE @ExecCmd VARCHAR(255)

     
DECLARE @y INT

     
DECLARE @x INT

     
DECLARE @FileContents
VARCHAR(MAX)

     
DECLARE @xmlHandle
INT

     
DECLARE @FILENAME
NVARCHAR(1000)

           
CREATE TABLE #configXML

                 
(

                   
PK INT NOT NULL

                              
IDENTITY(1, 1) ,

                   
[XMLValue] VARCHAR(MAX)

                 
)

SET @FileName = 'C:\Program Files\Microsoft SQL Server\MSRS11.SQL01\Reporting
Services\ReportServer\rsreportserver.config'

                
SET @ExecCmd = 'type ' + '"' + @FileName + '"'

                 
SET @FileContents
= ''

                       
INSERT  INTO
#configXML

                                         
EXEC MASTER.dbo.xp_cmdshell @ExecCmd

                                   
SELECT  @y
= COUNT(*)

                                  
FROM    #configXML

                 
SET @x = 0

                 
WHILE @x <> @y - 1

                                              
BEGIN

                       
                              SET @x = @x + 1

                                                     
SELECT  @FileContents
= @FileContents
+ [XMLValue]

                                                     
FROM    #configXML

                                                     
WHERE   PK
= @x

                                               
END

     
-- Display the file contents

           
SELECT  @FileContents
AS FileContents

           
DROP TABLE #configXML

    
-- Parsing the config file XML

     
EXEC sp_xml_preparedocument @xmlHandle OUTPUT, @FileContents

    
SELECT  *

    
FROM    OPENXML
(@xmlHandle, '//Service', 2) WITH

     (

     
IsSchedulingService VARCHAR(255) 'IsSchedulingService',

     
IsNotificationService VARCHAR(255) 'IsNotificationService',

     
PollingInterval VARCHAR(255) 'PollingInterval',

     
WindowsServiceUseFileShareStorage VARCHAR(255) 'WindowsServiceUseFileShareStorage',

      
  RECYCLETIME      
VARCHAR(255)      'RecycleTime'

      
  )

--This will provide you the value of your 2 parameters for
ReportServer URL and Report Manager URL

--The first value would be Report Server output and the 2nd value
is Remport Manager.

     
SELECT  *

     
FROM    OPENXML
(@xmlHandle, '//URL', 5) WITH

      (

     
UrlString VARCHAR(255) 'UrlString',

     
AccountName VARCHAR(255) 'AccountName'

    
)

      
 --This will give you important information
about authenticaion, MaxActiveReqForOneUser, and database timeout

 

      
       Select *

      
      From  OpenXML (@xmlHandle, '//Configuration',0)

             
        Where
ID in (854 ,855,856,857,74,75)

       --This will
provide extension informaiton

 

              SELECT
localname,text

              FROM
OPENXML(@xmlHandle, '//ModelGeneration',0)

              where
localname='#text'

 

EXEC sp_xml_removedocument @xmlHandle

 

 

How to get most frequent used top 10 reports on report server

SELECT TOP 10 COUNT(Name) AS ExecutionCount, Name,
SUM(TimeDataRetrieval) AS TimeDataRetrievalSum,
SUM(TimeProcessing) AS TimeProcessingSum,
SUM(TimeRendering) AS TimeRenderingSum,
SUM(ByteCount) AS ByteCountSum,
SUM([RowCount]) AS RowCountSum
FROM (SELECT TimeStart, Catalog.Type,Catalog.Name,TimeDataRetrieval, TimeProcessing,TimeRendering,ByteCount,[RowCount]
FROM
Catalog
INNER JOIN ExecutionLog ON Catalog.ItemID=ExecutionLog.ReportID
WHERE
ExecutionLog.TimeStart BETWEEN ‘2012-02-01 00:00:00.000’ AND ‘2013-03-28 23:59:00.000’
) AS RE
GROUP BY
Name
ORDER BY
COUNT(Name) DESC,
Name

How to get detailed execution history of reports on Report Server

SELECT  EL.TimeStart , EL.TimeEnd,DATEDIFF(s,EL.TimeStart , EL.TimeEnd) ‘Duration’ ,COALESCE(C.Path, ‘Unknown’) AS ItemPath, EL.UserName,EL.ExecutionId, CASE(EL.RequestType)
  WHEN 0 THEN ‘Interactive’  WHEN 1 THEN ‘Subscription’  WHEN 2 THEN ‘Refresh Cache’  ELSE ‘Unknown’END
   AS RequestType, — SubscriptionId
    EL.Format, Parameters, CASE(EL.ReportAction)   WHEN 1 THEN ‘Render’
    WHEN 2 THEN ‘BookmarkNavigation’  WHEN 3 THEN ‘DocumentMapNavigation’
     WHEN 4 THEN ‘DrillThrough’  WHEN 5 THEN ‘FindString’  WHEN 6 THEN ‘GetDocumentMap’  WHEN 7 THEN ‘Toggle’
      WHEN 8 THEN ‘Sort’  WHEN 9 THEN ‘Execute’  ELSE ‘Unknown’END AS ItemAction,EL.TimeStart, YEAR(EL.TimeStart)
       AS Start_Year,MONTH(EL.TimeStart) AS Start_Month,DATENAME(MONTH,EL.TimeStart) AS Start_Month_Name,
       DATENAME(DW,EL.TimeStart) AS Start_Day_Of_Week,DATEPART(WEEKDAY,EL.TimeStart)
       AS Start_Day_Number_of_Week,EL.TimeEnd, EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering,
       CASE(EL.Source)  WHEN 1 THEN ‘Live’  WHEN 2 THEN ‘Cache’  WHEN 3 THEN ‘Snapshot’   WHEN 4 THEN ‘History’
        WHEN 5 THEN ‘AdHoc’  WHEN 6 THEN ‘Session’  WHEN 7 THEN ‘Rdce’  ELSE ‘Unknown’END AS Source,EL.Status,
        EL.ByteCount,EL.[RowCount],EL.AdditionalInfo,C.Name,C.CreatedByID,C.ModifiedByID,C.Description,
        C.CreationDate,C.ModifiedDate,CASE   WHEN C.TYPE=1 THEN ‘Folder’  WHEN C.TYPE=2 THEN ‘Report’
        WHEN C.TYPE=3 THEN ‘XML’   WHEN C.TYPE=4 THEN ‘Linked Report’  WHEN C.TYPE=5 THEN ‘Data Source’
         WHEN C.TYPE=6 THEN ‘Model’  WHEN C.TYPE=8 THEN ‘Shared Dataset’  WHEN C.TYPE=9 THEN ‘Report Part’END
         AS Type_Description
          FROM   ExecutionLogStorage AS EL  LEFT OUTER JOIN Catalog AS C ON
          (EL.ReportID = C.ItemID)
          WHERE
          C.Path like ‘%QuickTrend%’
                    AND
          EL.TimeStart BETWEEN ‘2012-02-01 00:00:00.000’ AND ‘2013-02-28 23:59:00.000’
          ORDER By 1

How to get Snapshot associated with the report

SELECT Reportname = c.Name
,SubscriptionDesc=su.Description
,Subscriptiontype=su.EventType
,su.LastStatus
,su.LastRunTime
,Schedulename=sch.Name
,ScheduleType = sch.EventType
,ScheduleFrequency =
CASE sch.RecurrenceType
WHEN 1 THEN ‘Once’
WHEN 2 THEN ‘Hourly’
WHEN 4 THEN ‘Daily/Weekly’
WHEN 5 THEN ‘Monthly’
END
,su.Parameters
FROM ReportServer_SQL2008_Prod.dbo.Subscriptions su
JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
ON su.Report_OID = c.ItemID
JOIN ReportServer_SQL2008_Prod.dbo.ReportSchedule rsc
ON rsc.ReportID = c.ItemID
AND rsc.SubscriptionID = su.SubscriptionID
JOIN ReportServer_SQL2008_Prod.dbo.Schedule Sch
ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name = @ReportName

How to get subscription associated with the reports on Report Server

SELECT Reportname = c.Name
      ,SubscriptionDesc=su.Description
      ,Subscriptiontype=su.EventType
      ,su.LastStatus
      ,su.LastRunTime
      ,Schedulename=sch.Name
      ,ScheduleType = sch.EventType
      ,ScheduleFrequency =
       CASE sch.RecurrenceType
       WHEN 1 THEN ‘Once’
       WHEN 2 THEN ‘Hourly’
       WHEN 4 THEN ‘Daily/Weekly’
       WHEN 5 THEN ‘Monthly’
       END
       ,su.Parameters
  FROM ReportServer_SQL2008_Prod.dbo.Subscriptions su
  JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
    ON su.Report_OID = c.ItemID
  JOIN ReportServer_SQL2008_Prod.dbo.ReportSchedule rsc
    ON rsc.ReportID = c.ItemID
   AND rsc.SubscriptionID = su.SubscriptionID
  JOIN ReportServer_SQL2008_Prod.dbo.Schedule Sch
    ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name = @ReportName

How to get details of data sources and associated command with reports on Report Server

;WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition&#8217;,
http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition&#8217; AS REP
)
SELECT c.Path ,c.Name ,DataSetXML.value(‘@Name’, ‘varchar(MAX)’) DataSourceName ,
DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) CommandText
FROM ( SELECT ItemID ,CAST(CAST(Content AS VARBINARY(MAX)) AS XML) ReportXML
FROM ReportServer_SQL2008_Prod.[dbo].[Catalog]
WHERE TYPE = 2
) ReportXML
CROSS APPLY ReportXML.nodes(‘//REP:DataSet’) DataSetXML ( DataSetXML )
INNER JOIN [dbo].[Catalog] c ON ReportXML.ItemID = c.ItemID
— Search by part of the query text
WHERE ( DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) ) LIKE ‘%quicktrend%’

How to get the list of parameters used in Reports

SELECT a.name as ReportName,
Name = Paravalue.value(‘Name[1]’, ‘VARCHAR(250)’)
,Type = Paravalue.value(‘Type[1]’, ‘VARCHAR(250)’)
,Nullable = Paravalue.value(‘Nullable[1]’, ‘VARCHAR(250)’)
,AllowBlank = Paravalue.value(‘AllowBlank[1]’, ‘VARCHAR(250)’)
,MultiValue = Paravalue.value(‘MultiValue[1]’, ‘VARCHAR(250)’)
,UsedInQuery = Paravalue.value(‘UsedInQuery[1]’, ‘VARCHAR(250)’)
,Prompt = Paravalue.value(‘Prompt[1]’, ‘VARCHAR(250)’)
,DynamicPrompt = Paravalue.value(‘DynamicPrompt[1]’, ‘VARCHAR(250)’)
,PromptUser = Paravalue.value(‘PromptUser[1]’, ‘VARCHAR(250)’)
,State = Paravalue.value(‘State[1]’, ‘VARCHAR(250)’)
FROM (SELECT C.Name,CONVERT(XML,C.Parameter) AS ParameterXML
FROM ReportServer_SQL2008_Prod.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
–AND C.Name = ‘%/Dashboard/KnowledgeBase%’
) a
CROSS APPLY ParameterXML.nodes(‘//Parameters/Parameter’) p ( Paravalue )