Archive
SQL Server – Different Ways to Check Object Definition
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
If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – How to get last access/update time for a table
Modify date and create date for a table can be retrieved from sys.tables catalog view. When any structural changes are made the modify date is updated. It can be queried as follows:
USE [SqlAndMe] GO SELECT [TableName] = name, create_date, modify_date FROM sys.tables WHERE name = 'TransactionHistoryArchive' GO
sys.tables only shows modify date for structural changes. If we need to check when was the tables last updated or accessed, we can use dynamic management view sys.dm_db_index_usage_stats. This DMV returns counts of different types of index operations and last time the operation was performed.
It can be used as follows:
USE [SqlAndMe] GO SELECT [TableName] = OBJECT_NAME(object_id), last_user_update, last_user_seek, last_user_scan, last_user_lookup FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID('SqlAndMe') AND OBJECT_NAME(object_id) = 'TransactionHistoryArchive' GO
last_user_update – provides time of last user update
last_user_* – provides time of last scan/seek/lookup
It is important to note that sys.dm_db_index_usage_stats counters are reset when SQL Server service is restarted.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Difference between @@CONNECTIONS and @@MAX_CONNECTIONS
@@MAX_CONNECTIONS in SQL Server returns maximum number of simultaneous user connections allowed. Maximum user connections allowed by SQL Server by default is 32,767; this number also depends on application and server hardware limits. This cam also be configured at server-level to avoid too many connections.
@@CONNECTIONS returns number of connection attempts (successful/failed) made to SQL Server since SQL Server is started. Since this include all attempts it can be greater than @@MAX_CONNECTIONS.
SELECT [ConnectionAttempts] = @@CONNECTIONS,
[MaximumAllowed] = @@MAX_CONNECTIONS
Result Set:
ConnectionAttempts MaximumAllowed
394024473 32767
(1 row(s) affected)
As you can see from the example above number of connection attempts can be higher.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Calculating elapsed time from DATETIME
Elapsed time can be calculated from DATETIME field by extracting number of hours/minutes and seconds. You can use below query to calculate elapsed time between two dates:
-- Vishal - http://SqlAndMe.com DECLARE @startTime DATETIME DECLARE @endTime DATETIME SET @startTime = '2013-11-05 12:20:35' SET @endTime = '2013-11-10 01:22:30' SELECT [DD:HH:MM:SS] = CAST((DATEDIFF(HOUR, @startTime, @endTime) / 24) AS VARCHAR) + ':' + CAST((DATEDIFF(HOUR, @startTime, @endTime) % 24) AS VARCHAR) + ':' + CASE WHEN DATEPART(SECOND, @endTime) >= DATEPART(SECOND, @startTime) THEN CAST((DATEDIFF(MINUTE, @startTime, @endTime) % 60) AS VARCHAR) ELSE CAST((DATEDIFF(MINUTE, DATEADD(MINUTE, -1, @endTime), @endTime) % 60) AS VARCHAR) END + ':' + CAST((DATEDIFF(SECOND, @startTime, @endTime) % 60) AS VARCHAR), [StringFormat] = CAST((DATEDIFF(HOUR , @startTime, @endTime) / 24) AS VARCHAR) + ' Days ' + CAST((DATEDIFF(HOUR , @startTime, @endTime) % 24) AS VARCHAR) + ' Hours ' + CASE WHEN DATEPART(SECOND, @endTime) >= DATEPART(SECOND, @startTime) THEN CAST((DATEDIFF(MINUTE, @startTime, @endTime) % 60) AS VARCHAR) ELSE CAST((DATEDIFF(MINUTE, DATEADD(MINUTE, -1, @endTime), @endTime) % 60) AS VARCHAR) END + ' Minutes ' + CAST((DATEDIFF(SECOND, @startTime, @endTime) % 60) AS VARCHAR) + ' Seconds '
Result Set:
DD:HH:MM:SS StringFormat
4:13:2:55 4 Days 13 Hours 2 Minutes 55 Seconds
(1 row(s) affected)
[UPDATE] Earlier query had an error in calculation, thanks to Carlos for pointing it out and Nate for providing the correct solution.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page –> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe