Archive

Posts Tagged ‘sp_helptext’

SQL Server – Different Ways to Check Object Definition

14.04.2014 2 comments

sp_helptext is widely used for checking object definition in SQL Server. sp_helptext can be used to check definition of various database objects like Views, Stored Procedures and User Defined Functions.

There are two other options which can be used to retrieve object definition:

OBJECT_DEFINITION( object_id ) – is a built-in function. It can also retrieve definitions of CHECK/DEFAULT constraints

sys.sql_modules – is a catalog view which returns definitions of all modules in current database

Each of these can be used as follows:


USE [SqlAndMe]
GO

sp_helptext 'MyProcedure'
GO

-- Use OBJECT_ID() function to get object id
SELECT    OBJECT_DEFINITION(OBJECT_ID('MyProcedure'))
GO

-- Use OBJECT_ID() function to get object id
SELECT    [definition]
FROM    sys.sql_modules
WHERE    object_id = OBJECT_ID('MyProcedure')
GO

OBJECT_DEFINITION(object_id) and sys.sql_modules returns results as a single-line when in “Results to Grid” (Ctrl + D) mode. Switch to “Results to Text” (Ctrl + T) for formatted output which will include line breaks.

Hope This Helps!

Vishal

SQL Server – Cycle Error Logs for SQL Server and SQL Server Agent

08.02.2013 No comments

SQL Server error logs can fill up quickly, and when you are troubleshooting something you may need to go through huge log. However you can cycle the error log to manage the amount of log you need to go through. When you cycle error log the current log file is renamed from ERRORLOG to ERRORLOG.1, ERRORLOG.1 is renamed to ERRORLOG.2 and so on.

Restarting SQL Server will cycle the error logs. You can also use sp_cycle_errorlog to cycle error logs without restarting SQL Server.

Below is an example how to use it:

EXEC sp_Cycle_ErrorLog

GO

 

Result Set:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

You can verify that the log has cycled using Management Studio or you can use xp_ReadErrorLog also.

 

Same effect can be achieved by using DBCC ErrorLog. In fact, sp_Cycle_ErrorLog calls DBCC ErrorLog to cycle error logs.

DBCC ErrorLog

GO

 

Result Set:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

DBCC ErrorLog and sp_Cycle_ErrorLog only cycles SQL Server error logs. It does not cycle SQL Server Agent error logs. In order to achieve this you can restart SQL Server Agent or use another stored procedure sp_Cycle_Agent_ErrorLog.

USE   [msdb]

GO

 

EXEC  sp_Cycle_Agent_ErrorLog

GO

 

Result Set:

Command(s) completed successfully.

 

You can verify that the SQL Server Agent log has cycled using xp_ReadErrorLog.

* Use sp_helptext to see what is called from sp_Cycle_Agent_ErrorLog.

 

Hope This Helps!

Vishal