Archive

Posts Tagged ‘sys.tables’

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]
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

Using Catalog Views – sys.identity_columns

21.06.2011 No comments

sys.identity_columns contains a list of all identity columns in the database, some of the columns returned by sys.identity_columns are as below:

1. Object_id – object id of the parent table,

2. Name – column name for the identity column,

3. Column_id – id of the columns in the table,

4. Is_identity – 1,

5. Seed_value – Initial seed valued for the identity column,

6. Increment_value – Increment value specified for the column,

7. Last_value – last identity value that was used for the table,

8. Is_not_for_replication – 1, if the column was created with NOT FOR REPLICATION,

 

It can be used as:

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT object_id, name, column_id, is_identity,

       seed_value, increment_value, last_value, is_not_for_replication

FROM   sys.identity_columns

Partial Result Set:

image

 

To retrieve table name, it can be joined with sys.tables or OBJECT_NAME() function can be used as below:

SELECT OBJECT_NAME(object_id) AS 'Table Name', name, column_id, is_identity,

       seed_value, increment_value, last_value, is_not_for_replication

FROM   sys.identity_columns

Partial Result Set:

image

Hope This Helps!

Vishal

Using Catalog Views – sys.computed_columns

20.06.2011 No comments

sys.computed_columns returns all columns that are computed in the database, below are some of the columns returned by sys.computed_columns:

1. Object_id – object id of the table to which the column belongs,

2. Name – name of the computed column,

3. Column_id – id of the column in the respective table,

4. Definition – computation used to generate the value for column,

5. Is_persisted – 1, if the column is persisted,

6. Is_computed – 1

 

— © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT object_id, name, column_id, definition, is_persisted, is_computed

FROM   sys.computed_columns

Result Set:

image

 

To retrieve the Table Name to which the column belongs, you can join sys.computed_columns with sys.tables or use OBJECT_NAME() function as below:

SELECT OBJECT_NAME(object_id) AS 'Table Name', name, column_id,

       definition, is_persisted, is_computed

FROM   sys.computed_columns

Result Set:

image

Hope This Helps!

Vishal