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.
USE [YourDatabase] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Alter Procedure [dbo].[TableSize_Sp] @TableName varchar(500) as If object_id(@Tablename,'U') is not null Begin If Object_ID('tempdb..#TableSummary','U') Is Not Null Drop Table #TableSummary CREATE TABLE #TableSummary ( [database] varchar(500) default db_name(), table_name sysname , row_count BIGINT, reserved_size VARCHAR(50), data_size VARCHAR(50), index_size VARCHAR(50), unused_size VARCHAR(50), [timestamp] datetime default getdate() not null, Calendardate as convert(datetime,convert(varchar,[timestamp],101)) ) Declare @Sql varchar(max) Set @Sql=' INSERT #TableSummary ([table_name],[row_count],[reserved_size],[data_size],[index_size],[unused_size]) EXEC sp_spaceused''' + @TableName + ''' ' Exec (@Sql) If Object_ID('tempdb..#TableSummaryCleaned','U') is not null Drop Table #TableSummaryCleaned Select a.[Database], a.table_name [TableName], 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, [Timestamp], Calendardate Into #TableSummaryCleaned From ( Select [database], table_name , row_count, 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, [timestamp], [calendardate] From #TableSummary )a Group By a.[database], a.table_name, [Timestamp], Calendardate Alter Table #TableSummaryCleaned Add TotalMB float Update #TableSummaryCleaned Set TotalMB = ReservedSizeMB Alter Table #TableSummaryCleaned Add TotalGB float Update #TableSummaryCleaned Set TotalGB =TotalMB/1024 Select * From #TableSummaryCleaned End Else Begin Declare @ErrorText varchar(500) Set @ErrorText = @TableName+' is not a valid table.' RAISERROR(@ErrorText, 16, 1) End GO
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 Set @Counter=1 While @Counter <=1000 Begin Insert Into dbo.TestTable Select @counter*1000 Set @Counter=@Counter+1 End -- Check Table Size exec tablesize_sp'TestTable' -- Create Index Create NonClustered Index [NC_Numbers] on dbo.TestTable (numbers) -- Check size after creating index exec tablesize_sp'TestTable'
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.
|sp_helptext||View the contents of a stored procedure or function||Ctrl+3|
|tablesize_SP||Check the size of a table including indicies||Ctrl+4|
|sp_top10||View the top 10 records of a table||Ctrl+1|
|sp_help||View Information about an object for instance table schema||Alt+F1|
Note: SSMS 2012 was used for this example.