Archive for the ‘Catalog Views’ Category

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]

sp_helptext 'MyProcedure'

-- Use OBJECT_ID() function to get object id

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

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!


SQL Server – How to get last access/update time for a table

11.03.2014 2 comments

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]

SELECT    [TableName] = name,
FROM    sys.tables
WHERE    name = 'TransactionHistoryArchive'



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]

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'



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!


SQL Server – Kill all sessions using database

25.11.2013 3 comments

Before an existing database can be restored, there should be connections using the database in question. If the database is currently in use the RESTORE command fails with below error:

Msg 3101, Level 16, State 1, Line 2
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

To avoid this, we need to kill all sessions using the database. All sessions using the database can be queries using system stored procedure sp_who2 or using sys.dm_exec_sessions DMV:

SELECT   session_id
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = 'SqlAndMe'

You need to terminate each of the sessions returned individually by using KILL command.

If there are large number of sessions to kill, or you need to do this on a routine basis it gets boring to do it this way. You can *automate* this using below script, which takes database name as input, and kills all sessions connecting to it.

-- Kill all sessions using a database
-- Vishal -

USE [master]


SET @sqlCmd = ''
SET @dbName = 'SqlAndMe' -- Change database name here

SELECT   @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) +
FROM     sys.dm_exec_sessions
WHERE    DB_NAME(database_id) = @dbName

PRINT @sqlCmd

--Uncomment below line to kill
--EXEC (@sqlCmd)

Hope This Helps!


SQL Server – Get values as DATETIME from sysjobhistory

22.07.2013 No comments

This is a follow-up post on SQL Server – Check SQL Agent Job History using T-SQL.

SQL Server Agent stores SQL jobs history in sysjobhistory. It has two different columns for date and time, Run_Date and Run_Time. Since this is not available as DATETIME we cannot filter based on certain criteria such as jobs that run in last 24 hours.

There is a system function available in msdb database which takes these two columns as input converts output to a DATETIME data type, dbo.agent_datetime. You can use this function as below:

SELECT        TOP 5


       [StepName]    HIST.step_name,

              [RunDateTime] dbo.agent_datetime(HIST.run_date,HIST.run_time)

FROM          sysjobs JOB

INNER JOIN    sysjobhistory HIST ON HIST.job_id JOB.job_id

ORDERBY      HIST.run_dateHIST.run_time

Result Set:

JobName                  StepName                            RunDateTime

syspolicy_purge_history  Verify that automation is enabled.  2013-01-29 02:00:00.000

syspolicy_purge_history  Purge history.                      2013-01-29 02:00:00.000

syspolicy_purge_history  Erase Phantom System Health Records 2013-01-29 02:00:00.000

syspolicy_purge_history  (Job outcome)                       2013-01-29 02:00:00.000

syspolicy_purge_history  Verify that automation is enabled.  2013-01-30 02:00:00.000


(5 row(s) affected)

Hope This Helps!


SQL Server – How to get sizes of all databases on a server

10.06.2013 7 comments

To get database size information we can use sys.master_files catalog view. This view contains a row per file of a database for all databases.

The columns of interest for retrieving database size information are:

Column Name Description
database_id ID of the database to which the file belongs to
type_desc Description of file type. It can be ROWS, LOG, FILESTREAM or FULLTEXT
size File size in number of 8KB pages

Using this information we can retrieve database sizes using below query:

SELECT [Database Name] = DB_NAME(database_id),

       [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                     WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'

                     ELSE Type_Desc END,

       [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

FROM   sys.master_files

— Uncomment if you need to query for a particular database

— WHERE      database_id = DB_ID(‘Database Name’)



                     (DB_NAME(database_id), Type_Desc),



ORDER BY      DB_NAME(database_id), Type_Desc DESC


Result Set:

 Database Name        Type                 Size in MB

AdventureWorks2012   Data File(s)         189.00

AdventureWorks2012   Log File(s)          0.75

AdventureWorks2012   NULL                 189.75

Credit               Data File(s)         170.94

Credit               Log File(s)          10.00

Credit               NULL                 180.94

master              Data File(s)          4.00

master              Log File(s)           0.75

master              NULL                  4.75

model                Data File(s)         3.06

model                Log File(s)          0.75

model                NULL                 3.81

msdb                 Data File(s)         16.69

msdb                 Log File(s)          19.63

msdb                 NULL                 36.31

Northwind            Data File(s)         4.25

Northwind            Log File(s)          3.06

Northwind            NULL                 7.31

pubs                 Data File(s)         3.25

pubs                 Log File(s)          3.06

pubs                 NULL                 6.31

SqlAndMe             Data File(s)         137.88

SqlAndMe             Log File(s)          19.13

SqlAndMe             NULL                 157.00

tempdb              Data File(s)          8.00

tempdb              Log File(s)           0.50

tempdb              NULL                  8.50


(27 row(s) affected)

The above query gets sizes for Data Files and Log Files and displays a total using GROUPING SETS.

Hope This Helps!


SQL Server – How to Move Table to Another Schema

13.05.2013 1 comment

Starting with SQL Server 2005 all tables are grouped into schemas. While creating a table if the schema name is not specified it is created in the default schema of the user creating it. you can use ALTER SCHEMA command to move tables between schemas.

For example, if I create a table using below script it will be created under my default schema which is dbo:

USE [SqlAndMe]





       ID     INT,

       Name VARCHAR(20)



SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'


Result Set:

name          schema

Employee      dbo


(1 row(s) affected)

As you can see from the output the table is currently in dbo schema.

Now to move this table to another schema using ALTER SCHEMA command, first we need to create the schema if it does not exist already. After that we can move table to new schema.

USE [SqlAndMe]



CREATE SCHEMA HumanResources



ALTER SCHEMA HumanResources

TRANSFER dbo.Employee



SELECT name, [schema] = SCHEMA_NAME(schema_id)

FROM   sys.tables

WHERE  name = 'Employee'


Result Set:

name          schema

Employee      HumanResources


(1 row(s) affected)

As you can see from the output the Employee table is now moved to HumanResources schema.


Hope This Helps!


SQL Server – Finding out Database creation time

19.02.2013 1 comment

Finding database creation time is simple if database has not been detached from server. You can find database creation time using below methods.

Using sp_helpdb:

You can find the creation time of database using sp_hepldb system procedure.

EXEC sp_helpdb 'SqlAndMe'


This will return database creation time along with other details:


Using catalog view sys.databases:

Database creation time is also available in sys.databases catalog view:

SELECT      create_date

FROM        sys.databases

WHERE       name = 'SqlAndMe'


Result Set:


2012-11-14 15:19:31.987


(1 row(s) affected)


However, if the database has been detached and reattached to server, the creation time will be changed to attach time. Also, if the database has been restored from a backup after dropping the database creation time will be changed.

Actual creation time of database is stored in the boot page of the database which is retained after restore or detach/attach. This is stored as dbi_crdate.

You can use DBCC PAGE to read creation time of database from boot page:




DBCC PAGE('SqlAndMe', 1, 9, 3)



Result Set (Trimmed):

dbi_createVersion = 661              dbi_ESVersion = 0                   

dbi_nextseqnum = 1900-01-01 00:00:00.000        dbi_crdate = 2011-11-07 21:12:46.357

dbi_filegeneration = 2              



Hope This Helps!


SQL Server – Check SQL Agent Job History using T-SQL

14.05.2012 4 comments

To check SQL Server Agent Job History you can use the Log File Viewer from SQL Server Management Studio.

To Open Log File Viewer,

1. Expand Server Node > 2. Expand SQL Server Agent > 3. Expand Jobs > 4. Right click on the Job and 5. Select "View History" as shown in the screen shot below:



Alternatively, you can also use below T-SQL code to check Job History:

SELECT      [JobName]   =,

            [Step]      = HIST.step_id,

            [StepName]  = HIST.step_name,

            [Message]   = HIST.message,

            [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'

            WHEN HIST.run_status = 1 THEN 'Succeeded'

            WHEN HIST.run_status = 2 THEN 'Retry'

            WHEN HIST.run_status = 3 THEN 'Canceled'


            [RunDate]   = HIST.run_date,

            [RunTime]   = HIST.run_time,

            [Duration]  = HIST.run_duration

FROM        sysjobs JOB

INNER JOIN  sysjobhistory HIST ON HIST.job_id = JOB.job_id

/* WHERE = 'Job1' */

ORDER BY    HIST.run_date, HIST.run_time


JobName  Step StepName      Message       Status     RunDate   RunTime   Duration

Job1     1    Step1         Executed a..  Succeeded  20120416  173935    10

Job1     0    (Job outcome) The job su..  Succeeded  20120416  173935    10

Job2     1    Step1         Executed a..  Succeeded  20120416  174037    10

Job2     0    (Job outcome) The job su..  Succeeded  20120416  174037    10


Check dbo.sysjobs and dbo.sysjobhistory on BOL for more information.

Hope This Helps!