The moment you would have read the title of this post, you would have thought – “What’s so special in this post?” Well, did you know that there are not one, not two, but at least three (3) distinct methods that you can use to get the definition/script for any database object in Microsoft SQL Server using T-SQL?

Surprised? Read on…

Method #01 – sys.syscomments (obsolete)

For anyone who has been around since (or perhaps before) the days of SQL 2000, sys.syscomments must be familiar. This system table contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. Here’s how one would use it:

SELECT sc.text

FROM sys.syscomments AS sc

INNER JOIN sys.objects AS so ON sc.id = so.object_id

WHERE so.name = ‘uspUpdateEmployeePersonalInfo’

  AND so.schema_id = SCHEMA_ID(‘HumanResources’)

GO

For the purposes of this demonstration, I have switched the query results output to Text and changed the max. number of characters for text output to 8000. The output is shown below:

System tables have already been marked for deprecation in future releases of Microsoft SQL Server. Hence, starting SQL Server 2005, it is no longer advised to use the sys.syscomments system table.

Method #02 – sp_helptext

One of the drawbacks of using sys.comments is that the formatting of the script may be lost. Another hot favourite – a system stored procedure sp_helptext, returns a fairly formatted version of the script. Here’s how to get the definition for HumanResources.uspUpdateEmployeePersonalInfo.

USE AdventureWorks2012

GO

EXEC sp_helptext ‘HumanResources.uspUpdateEmployeePersonalInfo’

GO

/*Alternate methods:

EXEC sp_helptext [HumanResources.uspUpdateEmployeePersonalInfo]

GO
–The following does not work, both the schema name and the object name need to be one identifier:

EXEC sp_helptext [HumanResources].[uspUpdateEmployeePersonalInfo]

GO

*/

The output looks like the one shown below:

sp_helptext continues to use the sys.syscomments system table. However, the only good thing about using a system stored procedure rather than directly querying a system table is that whenever Microsoft changes the implementation of sp_helptext, any existing scripts that use the system stored procedure would not need to change.

Method #03(A) – OBJECT_DEFINITION()

This T-SQL function returns the object data using the meta-data information associated with the specified object. Instead of worrying about using the schema name and the object name as one identifier, the usage here is much simpler because one has to deal only with the OBJECT_ID. Getting the definition of any object is as simple as executing a simple SELECT statement now:

USE AdventureWorks2012

GO

SELECT OBJECT_DEFINITION(OBJECT_ID(‘HumanResources.uspUpdateEmployeePersonalInfo’)) AS ObjectText

GO

The result is as shown below. OBJECT_DEFINITION does not query sys.syscomments, instead directly queries the metadata associated to the database.

sp_helptext v/s OBJECT_DEFINITION: A Comparison

Given that sys.syscomments is obsolete and should not be used, I believe it would be a good time to compare the two remaining options – sp_helptext and OBJECT_DEFINITION.

Object type	sp_helptext	OBJECT_DEFINITION
CHECK constraint	Y	Y
Default (or stand-alone) constraint	Y	Y
Default, unencrypted stored procedure	Y	Y
SQL Scalar functions	Y	Y
Rules	Y	Y
Replication filter procedures	Y	Y
Triggers	Y	Y
In-line TVFs	Y	Y
Multi-line TVFs	Y	Y
Views	Y	Y
Computed Columns	Y	N

All is well until the computed columns show up. OBJECT_DEFINITION clearly lags behind sp_helptext in this area.

Method #03(B) – Using sys.computed_columns

To get the definition of computed columns, a separate metadata visibility view exists, named the sys.computed_columns. Here’s a sample implementation:

USE AdventureWorks2012

GO

SELECT object_id,

       name,

       definition,

       column_id,

       system_type_id,

       max_length,

       precision,

       scale,

       collation_name

FROM sys.computed_columns

GO

The results is as shown below:

Now that we have seen all the 3 methods that I know of, I would like to say that my favourite one (probably out of habit) is sp_helptext. The question to you is:

Which method do you use to fetch the object definition?

Do leave a note as you go.

EDIT [09/10/2012]: My dear friend, Chintak just wrote a post extending this topic. It’s a great read and you can find it here: http://beyondrelational.com/modules/2/blogs/115/posts/17659/sphelptext-vs-objectdefinition.aspx

[END EDIT]

References

• sys.syscomments: http://msdn.microsoft.com/en-us/library/ms186293.aspx
  
• sp_helptext: http://msdn.microsoft.com/en-us/library/ms176112.aspx
  
• OBJECT_DEFINITION: http://msdn.microsoft.com/en-us/library/ms176090.aspx
  
• sys.computed_columns: http://msdn.microsoft.com/en-us/library/ms188744.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Those who have been following this site regularly would know that a 10-part tutorial series on the SQL Server Profiler, authored by me just was recently published. You can find the master page here. This week, I would try to address some of the questions that have come from you, the kind readers as part of this series or in the “ASK” module of this site.

The question that I would address today is:

How can one filter trace events based on the Windows Login/User Name?

This is a very important requirement, especially when troubleshooting terminal-server based environments. Normally, in development environments, simply filtering on the host name is sufficient. However, in a terminal server configuration, multiple users may be logged on to the same host, which makes filtering on the windows login/username important.

Preparing the Environment

Before I execute the tests, allow me to prepare my test environment. I will be creating one stored procedure, which will be executed by 3 different windows users.

USE AdventureWorks2012
GO
CREATE PROCEDURE HumanResources.proc_GetEmployes
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT SYSTEM_USER,
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

As seen in the screenshot below, we have 3 Windows Users which have been granted access to SQL Server. (NOTE: In practice, it is not advisable to add users to the sysadmin fixed server role. This has been done here for demonstration purposes only):

1. WIN2K8Administrator
2. WIN2K8SQLTestUser01
3. WIN2K8SQLTestUser02

Executing the Test

To execute the test, I have already prepared the SQL Server Profiler to use a custom template based on the “TSQL_SPs” default template as shown in the tutorial: getting-started-with-sql-server-profiler-part-2-profiler-templates-template-types-and-creating-c. As part of the customization, I have chosen to capture and display the following four columns:

• LoginName
• LoginSid
• NTDomainName
• NTUserName

Now that the SQL Server Profiler is ready, I will execute the stored procedure under the context of the 3 users referenced above. To switch the execution context, I have used the EXECUTE AS LOGIN clause.

USE AdventureWorks2012
GO
--Executing as the first test user - WIN2K8SQLTestUser01
EXECUTE AS LOGIN = 'WIN2K8SQLTestUser01'
EXEC HumanResources.proc_GetEmployes @businessEntityId = 1
GO
--Revert to original logged-in user
REVERT
GO

--Executing as the first test user - WIN2K8SQLTestUser02
EXECUTE AS LOGIN = 'WIN2K8SQLTestUser02'
EXEC HumanResources.proc_GetEmployes @businessEntityId = 2
GO
--Revert to original logged-in user
REVERT
GO

EXECUTE AS LOGIN = 'WIN2K8Administrator'
EXEC HumanResources.proc_GetEmployes @businessEntityId = 3
GO
--Revert to original logged-in user
REVERT
GO

Looking at the Profiler, I can see that I have been able to capture the fact that the statements were executed under 3 different user contexts.

To filter the results, all one has to do is to filter one of the four columns mentioned below (as applicable):

• For both SQL & Windows Logins:
• LoginName
• LoginSid
• For Windows Logins:
• NTDomainName
• NTUserName

Trust that the above exercise has cleared the queries of all my dear readers. If you still have any queries, do send them across – I will be happy to help you out.

References:

• EXECUTE AS: http://msdn.microsoft.com/en-us/library/ms181362.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Those who have been following this site regularly would know that a 10-part tutorial series on the SQL Server Profiler, authored by me just was recently published. You can find the master page here. This week, I would try to address some of the questions that have come from you, the kind readers as part of this series or in the “ASK” module of this site.

The question that I would address today is:

How can I filter multiple objects of the same type?

For example, add a condition like ObjectName NOT LIKE ‘%SP1%’ AND ObjectName NOT LIKE ‘%SP2%’

The SQL Server Profiler is a very powerful performance analysis tool, and this is a fairly common requirement. Here’s a step-by-step guide to realize this requirement. For this demo, let me create 3 very stored procedures. Before we execute these procedures, we will use the Profiler to filter out two of them from the trace.

USE AdventureWorks2012
GO
CREATE PROCEDURE HumanResources.proc_GetEmployes1
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP1',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

CREATE PROCEDURE HumanResources.proc_GetEmployes2
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP2',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

CREATE PROCEDURE HumanResources.proc_GetEmployes3
    @businessEntityId INT
AS
BEGIN
    SET NOCOUNT ON

    SELECT 'SP3',
           e.BusinessEntityID,
           e.BirthDate,
           e.HireDate,
           e.JobTitle
    FROM HumanResources.Employee AS e
    WHERE e.BusinessEntityID = @businessEntityId
END
GO

Now that these procedures are created, let us setup the Profiler to capture the stored procedure execution, filtered such that only the procedure is seen in the trace.

1. Follow the steps provided in the Profiler Tutorial Part #2: http://beyondrelational.com/modules/12/tutorials/631/tutorials/15252/getting-started-with-sql-server-profiler-part-2-profiler-templates-template-types-and-creating-custo.aspx to create a customized template
   • I generally prefer using the “TSQL_SPs” default template as the base and then customize it to suite the requirement
2. When customizing the column filters, add the first line:
3. 
4. Now, hit enter to add a new line
5. 
6. Now, add the second line of filter
7. 
8. Click “OK” to complete configuration of the template

Now, start the Profiler trace and execute the following query:

USE AdventureWorks2012
GO
EXEC HumanResources.proc_GetEmployes1 @businessEntityId = 1
GO
EXEC HumanResources.proc_GetEmployes2 @businessEntityId = 2
GO
EXEC HumanResources.proc_GetEmployes3 @businessEntityId = 3
GO

The Results tab of the SQL Server Management Studio (SSMS) will show the results from the execution of all 3 procedures.

The Profiler would capture the following trace, which only captures the activity from the stored procedure HumanResources.proc_GetEmployees2 and not from the other two procedures. Adding new lines to the Profiler template column filters makes the Profiler to apply an “AND” condition on the filter.

I hope the above simple steps help you to filter your Profiler Results efficiently.

Until we meet next time,

Be courteous. Drive responsibly.

It’s close to a week-end, and hence, I have a very short post for today.

Very often I find teams and SQL Server enthusiasts coming up to me and asking me some of the following questions:

• How many databases can I host on a SQL Server instance?
  
• How large can these databases be?
  
• Is there a limit to the number of records in a table?

I often point them to Books On Line/MSDN where they can find the official specification which would be able to answer all of their questions. Today, I thought of putting an end to this almost endless cycle of same question-answer sessions and provide the direct links to the Books on Line/MSDN maximum capacity specification for SQL Server. So, here goes:

Maximum Capacity Specifications for SQL Server

• SQL 2012: http://msdn.microsoft.com/en-us/library/ms143432.aspx
  
• SQL 2008 R2: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.105).aspx
  
• SQL 2008: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.100).aspx
  
• SQL 2005: http://msdn.microsoft.com/en-us/library/ms143432(v=sql.90).aspx

How do you calibrate your systems?

Whenever someone asks a minimum/maximum capacity specification related question, I always wonder how they would calibrate their systems and more importantly, certify their product against these numbers. So, here’s what I would like to learn from you, my kind readers:

How do you derive minimum/maximum capacity specifications for your systems?

Ideally, the situation would be that a production system would have the same limitations as the underlying platform. But, that would not happen in practice due to the choice of the architecture and/or technical design decisions.

Do leave a note as you go. It’s a very interesting question, with no fixed answer – every system and every team would have their own methods, which is why I am sure it would be a great discussion.

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is a very simple tip based on a question I received from one of my colleagues. They had dates stored in their database and wanted to extract day of the week and day of the year information from the date column.

Now, this would have been a very easy task for the team had this requirement been related to displaying the day of the week and day of the year information on the UI. But, in this particular case, they had to dump it out to a text file (using SQL Server BCP) for exchanging with a legacy reader-board feeding system (If you are unfamiliar with the term, the display boards on railway stations and airports that inform travellers about the schedule and other stages of the boarding process and the associated data stores are reader-board systems). When they approached me, they told me it was a big problem and were wondering if I could spend a day with them to help them figure things out. When I said it won’t even take more than 10 minutes, they were awestruck!

The DATEPART function

Most of us have worked with T-SQL date manipulation functions in Microsoft SQL Server, with the most common use being extracting parts like date, month and year from the date or a date/time value. However, there are many more options, which are what I shared with the team.

Besides extracting parts of a date, the DATEPART function can also help us determine the following for a given date or date/time value:

• The Day of the Week
• The Day of the Year
• Week number
• ISO Week Number

Here’s the associated query:

USE tempdb
GO
DECLARE @currentDate DATE = GETDATE()

SELECT @currentDate                      AS CurrentDate,
       DATEPART(DAYOFYEAR, @currentDate) AS DayOftheYear,
       DATEPART(WEEKDAY  , @currentDate) AS DayOftheWeek,
       DATEPART(WEEK     , @currentDate) AS WeekNumber,
       DATEPART(ISO_WEEK , @currentDate) AS ISOWeekNumber
GO

/*
DAYOFYEAR --Equivalent interval identifiers: DY
WEEKDAY   --Equivalent interval identifiers: DW
WEEK      --Equivalent interval identifiers: W
ISO_WEEK  --Equivalent interval identifiers: ISOWK, ISOWW
*/

For a test value of ‘2012-08-25’, the results are:

CurrentDate	DayOftheYear	DayOftheWeek	WeekNumber	ISOWeekNumber
2012-08-25	238	7	34	34

All the team had to do was to incorporate the DATEPART function call in their T-SQL queries. There – did it take more than 10 minutes?

Further Reading

If you would like to explore all the options of the DATEPART function, I would redirect you to the Books-On-Line page: http://msdn.microsoft.com/en-us/library/ms174420.aspx

Also, if you would like to read more about how the team exported bulk data from their SQL Server tables into a text/comma-separated file, you may refer my previous posts:

• BCP & Bulk Inserts – Underappreciated features of Microsoft SQL Server
• SSMS – Query Result configuration – Generate comma separated output without BCP or query changes

Until we meet next time,

Be courteous. Drive responsibly.