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
Category Archives: TSQL Scripts
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. 
>>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’,
‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’ 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 )
coresql.in 