Reading Time: 3 minutes
Continuing with last week's post about Helper Stored Procedures, I wanted to introduce two more that I use frequently. The first one is a built in stored procedure ( using SQL Server) called 'sp_helptext'. This stored procedure allows you to view the content of other stored procedures (SP) and more, here is a link to the Microsoft documentation. Here is an example of how you use it:
This will show you the contents of sp_helptext by using it. This comes in very handy as a keyboard shortcut. I assigned mine to 'Ctrl+3'. Often I have a SP that does data manipulation and puts the final data in a table that occurs as the last step of my SP. I may not remember which table the data goes into, but I can simply highlight the SP ( I typically double click) and then use 'Ctrl+3' and the contents of the SP appears in my results tab. This also works nicely for function and triggers that you might use.
Another piece of information that I am often curious about is how large is a particular table. If you like going through menus, then you can left click on a table name in the object explorer and choose properties. Then select 'storage' from the left navigation menu. You will see under the 'general' section: "Data Space","Row Count" and "Index Space". While this is okay if you only perform this task once a month, it takes way to long if you are running this 5 + times per day. I wrote a stored procedure called 'sp_tablesize' that is a wrapper for another Microsoft SP, that is sp_spaceused.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
Alter Procedure [dbo].[TableSize_Sp]
If object_id(@Tablename,'U') is not null
If Object_ID('tempdb..#TableSummary','U') Is Not Null
Drop Table #TableSummary
CREATE TABLE #TableSummary
[database] varchar(500) default db_name(),
table_name sysname ,
[timestamp] datetime default getdate() not null,
Calendardate as convert(datetime,convert(varchar,[timestamp],101))
Declare @Sql varchar(max)
INSERT #TableSummary ([table_name],[row_count],[reserved_size],[data_size],[index_size],[unused_size])
EXEC sp_spaceused''' + @TableName + '''
If Object_ID('tempdb..#TableSummaryCleaned','U') is not null
Drop Table #TableSummaryCleaned
round(sum(cast(a.row_count as bigint)),2)[Rows],
round(sum(cast(reserved_size_kb as float)/1024),2)ReservedSizeMB,
round(sum(cast(data_size_kb as float)/1024),2)DataSizeMB,
round(sum(cast(index_size_kb as float)/1024),2)IndexSizeMB,
round(sum(cast(unused_size_kb as float)/1024),2)UnusedSizeMB,
cast(replace(reserved_size, ' KB','') as bigint) reserved_size_kb,
cast(replace(data_size, ' KB','') as bigint) data_size_kb,
cast(replace(index_size, ' KB','') as bigint) index_size_kb,
cast(replace(unused_size, ' KB','') as bigint) unused_size_kb,
Alter Table #TableSummaryCleaned
Add TotalMB float
Set TotalMB = ReservedSizeMB
Alter Table #TableSummaryCleaned
Add TotalGB float
Set TotalGB =TotalMB/1024
Declare @ErrorText varchar(500)
Set @ErrorText = @TableName+' is not a valid table.'
RAISERROR(@ErrorText, 16, 1)
After you create the SP 'tablesize_SP' you can assign it to a shortcut in SSMS. I choose to use 'Ctrl+4' for myself. We can create a table and add data and an index to make sure that our new SP works.
Create Table dbo.TestTable
( numbers float )
-- Create Table with numbers 1 -100
Declare @Counter int
While @Counter <=1000
Insert Into dbo.TestTable Select @counter*1000
-- Check Table Size
-- Create Index
Create NonClustered Index [NC_Numbers] on dbo.TestTable
-- Check size after creating index
You will notice that our index size increased after adding one, which is to be expected. Hopefully this will allow you to quickly check the size of a table. Combine that with 'sp_helptext' and 'sp_top10' you can find out a lot of information about a table quite quickly. Let me leave you with a quick reference guide of which SP to use depending on what you want to know.
||View the contents of a stored procedure or function
||Check the size of a table including indicies
||View the top 10 records of a table
||View Information about an object for instance table schema
Note: SSMS 2012 was used for this example.