SSMS 2012–SP_HelpText workaround

 

There is a well-known issue in SQL Server Management Studio 2012 with sp_helptext: It puts extra lines in grid result set. This problem is known by the Microsoft since 2011 and it seems that they really do  not want to fix it until the next version of SQL Server.

I will show how to reproduce the problem and how to make a workaround for it.

I: Reproduce the problem:

1: Open SQL Server Management Studio 2012

2: Create a sample stored procedure:

DoSomething

1. create procedure dbo.DoSomething (
2.     @string nvarchar(100)
3. )
4. as begin
5.     set nocount on
6.  
7.     if @string is null or @string = ''
8.         return 1
9.  
10.     select @string
11. end
12. go

3: Run SP_HelpText with Results to Grid mode:

- Make sure that Results to Grid option is set:

- Run sp_helptext:

exec usp_helptext 'usp_helptext'

- Copy the resultset, and paste into the code window:

As we can se, as soon as we pasted the result into our code window, each line got an extra row.

 

II. Possible solutions:

- Most famous on the internet: use the Results to Text (CTRL-T) option and copy from there.

- Create our own version from the sp_helptext stored procedure.

- Switch to SSMS 2008R2

- Wait until the next version of SQL Server is released.

* Extra solution: use regexp replace on the copied result (we just have to replace all \n\n patterns to \n

I think there is no really good solution for the problem: Using Results to Text option means that we always have to switch between the text and grid mode during our work. As a database engineer I think that is only waste of time. We can create an own version of sp_helptext. The advantage is that we can use it really easy and we have a full control on the solution (that is why I have chosen this method. The disadvantage is that we have to compile our custom procedure in each database that we use. Of course we can switch to the older version of Management Studio too but I am not sure backward upgrading can be a proper solution for anything, and of course we can wait until the next release but if we work with it every day we must have a fix as soon as possible. And of course, we can use regexp replace but who has such time?

 

III. Let se usp_helptext

After I decided to create a custom version, I sat down and created the following script:

usp_helptext

1. drop procedure dbo.usp_helptext
2. go
3.  
4. create procedure dbo.usp_helptext(
5.   @SPName sysname
6. )
7. as begin
8.   set nocount on
9.  
10.   declare @LocalSPName varchar(100)
11.   declare @Definition nvarchar(max)
12.   declare @NewLine nchar(2) = CHAR(13) + CHAR(10)
13.   declare @SPDefTable table (RowNumber int identity(1,1) primary key, Row nvarchar(500) not null)
14.   declare @NLPos int
15.   declare @Row nvarchar(max)
16.   declare @Length int
17.  
18.   if @SPName is null or @SPName = ''
19.     return
20.  
21.   set @LocalSPName = @SPName
22.   select @Definition = definition from sys.all_sql_modules with(nolock) where object_id = object_id(@LocalSPName)
23.  
24.   if @@ROWCOUNT = 0
25.     return
26.  
27.   set @Length = LEN(@Definition)
28.  
29.   while @Definition > ''
30.   begin
31.     set @NLPos = PATINDEX('%' + @NewLine + '%', @Definition)
32.  
33.     if @NLPos <> 0
34.     begin
35.       set @Row = LEFT(@Definition, @NLPos - 1)
36.       set @Definition = SUBSTRING(@Definition, @NLPos + 2, @Length)
37.     end
38.     else
39.     begin
40.       break;
41.     end
42.  
43.     insert into @SPDefTable (Row)
44.     select @Row
45.   end
46.  
47.   if @Definition > ''
48.     insert into @SPDefTable (Row)
49.     select @Definition
50.  
51.   select Row
52.   from @SPDefTable
53.   order by RowNumber
54. end
55. go

As we can see, I have to learn a lot about programming sql server. This solution is based on my structured programming studies: It uses a loop and conditional statements. I made so of course it works fine but it is not a good-looking solution if your solution is set-based. It must contain some selects or it not a solution .
When I have finished with my usp_helptext procedure, I showed it to my boss. He likes challenges so immediately started to type something. When he finished, he send his solution to me and I started to test it. After I recognized all the boundary cases the transact-sql solution has born:

usp_helptext

1. drop procedure dbo.usp_helptext
2. go
3.  
4. create procedure dbo.usp_helptext(
5.   @SPName varchar(100)
6. )
7. as begin
8.   set nocount on
9.  
10.   declare @LocalSPName varchar(100)
11.   declare @Definition nvarchar(max)
12.   declare @NewLine nchar(2) = CHAR(13) + CHAR(10)
13.   declare @Length int
14.   declare @ErrMsg nvarchar(200)
15.  
16.   if @SPName is null or @SPName = ''
17.     return
18.  
19.   set @LocalSPName = @SPName
20.   select @Definition = definition from sys.all_sql_modules with(nolock) where object_id = object_id(@LocalSPName)
21.  
22.   if @@ROWCOUNT = 0
23.   begin
24.     set @ErrMsg = N'Cannot find stored procedure: ' + @LocalSPName
25.  
26.     raiserror (@ErrMsg, 16, 1)
27.   end
28.  
29.   set @Length = LEN(@Definition)
30.  
31.   ;with a as
32.   (
33.     select cast(1 as bigint) start, PATINDEX('%' + @NewLine + '%', @Definition) curlength, 0 as stopthere
34.     union all
35.     select a.start+a.curlength, PATINDEX('%' + @NewLine + '%', SUBSTRING(@Definition, a.start+a.curlength, @Length)) , case when PATINDEX('%' + @NewLine + '%', SUBSTRING(@Definition, a.start+a.curlength, @Length)) = 0 then 1 else 0 end
36.     from a
37.     where a.start+a.curlength < @Length and a.stopthere = 0
38.   )
39.   select REPLACE(REPLACE(SUBSTRING(@Definition, a.start, a.curlength), CHAR(10), ''), CHAR(13), '')
40.   from a
41.   option (maxrecursion 0)
42.  
43. end
44. go

I am proud to this solution. It is really a set-based solution and it shows what is the difference between an usual programmer and an sql developer. The golden rule is: If we want to use a loop we just have to sit down and search a proper select statement.

The real multi-lingual site

We can find hungarian, english and german languages in one form.

Sql Server–Row Count for All Tables

 

Tested on Sql Server 2008 and 2008 R2.

Code Snippet

1. select SCHEMA_NAME(sys.tables.schema_id) as [Schema], sys.tables.name as [Table], sys.sysindexes.rowcnt as [Cnt]
2. from sys.indexes
3.     join sys.sysindexes
4.         on sysindexes.id = sys.indexes.object_id
5.             and sysindexes.indid = sys.indexes.index_id
6.     join sys.tables
7.         on sys.tables.object_id = sys.sysindexes.id
8. where is_primary_key = 1
9.     and sys.tables.type_desc = 'USER_TABLE'
10. order by [Schema], [Table]

It is simple, isn’t it?

Looking for unused indexes in Sql Server 2008

 

In a long running project the database is changing day by day. We create new tables, optimize our queries, and of course, make new indexes. Usually when I finish an optimization process I forget an important step:

I always forget to check how many indexes I have that I don’t use anymore.

Sql server stores index statisctics in a sys.dm_ view named SYS.DM_DB_INDEX_USAGE_STATS. As the other sys.dm_ views, sql server clears statistics on every restart, so we have to check after missing indexes only for our database instance had run for significant time and we had collected enough statistics to decide if we delete an index or not.

Now after we have understood the basic, let’s see the query for showing our index statistics:

DECLARE @table_name varchar(100) = null

-- Uncomment the following line if you want to filter for a given table
--set @table_name = 'table_name'

SELECT
         OBJECT_NAME(I.[OBJECT_ID]) AS [OBJECT NAME],
         I.[object_id],
         I.[NAME] AS [INDEX NAME],
         sum(USER_SEEKS) as USER_SEEKS,
         sum(USER_SCANS) as USER_SCANS,  
         sum(USER_LOOKUPS) as USER_LOOKUPS,
         sum(USER_UPDATES)  as USER_UPDATES
     FROMSYS.INDEXES AS I
         LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS AS S
           ON I.[OBJECT_ID] = S.[OBJECT_ID]
              AND I.INDEX_ID = S.INDEX_ID
  WHEREOBJECTPROPERTY(I.[OBJECT_ID],'IsUserTable') = 1
    and (@table_name is null or OBJECT_NAME(I.[OBJECT_ID]) = @table_name)

group by OBJECT_NAME(I.[OBJECT_ID]),
         I.[object_id],
         I.[NAME]
    order by [OBJECT NAME], [INDEX NAME]

As we can see on our column names, we access seek, scan, lookup and index update statistics counted since the last start of our sql server instance. Our result can contain null statistics. In that case we don’t have any statistics about the given index since the last restart.

This script contains one parameter (@table_name). If we uncomment the 4th line, we can filter for indexes only on a given table.

4000 visitors

4000 visitors! :)

I work on 3 projects during this month and don't have a time to write.
The life continues on April.

Morzel

ProgrammerFail - Else or Continue?

 

if (value < 100)
{
    value = 100 - value;
    continue;   // Why???
}
else
{
    value = value + 100;
}

Double penetration – I really don’t want to run the else clause…

!!!FAIL!!!

ProgrammerFail–Using System Functions

 

Nice example that shows what happens if we don’t have enough time to read a book about ‘Teach Yourself SQL Server Programming in 24 hours’ and start programming without knowing the tool that we use.

After we check msdn, we can easily check if two dates are in the same day:

create FUNCTION [dbo].[isSameDay](@DATE1 datetime, @DATE2 datetime)
RETURNS bit
AS
BEGIN
    DECLARE @result bit=0
    if (year(@DATE1)=year(@DATE2))
    and (month(@DATE1)=month(@DATE2))
    and (day(@DATE1)=day(@DATE2))
    set @result=1
    return @result
END

!!!FAIL!!!