MS SQL Server - How to get the last change date of table data and schema?

Hi Everyone,

Do you need to know when was the last change (Insert, Update or Delete) happened in the SQL Server table data and the table meta data (schema change)?

If you are searching for answers for the above questions, here I am with the answers.

Script to find the last schema change date of a table, you will need to supply table name in the where clause. Run this query in the target database.

SELECT

NAME AS TABLENAME ,

MODIFY_DATE AS LAST_SCHEMA_CHANGE

FROM    SYS.OBJECTS

WHERE   NAME = ‘<TABLE>’

Script to find when the table data was last changed, here you will need to specify the database name and the table number. You may require admin privilege to run this query.

SELECT  

OBJECT_NAME(OBJECT_ID) AS TABLENAME ,

LAST_USER_UPDATE ,

USER_UPDATES ,

INDEX_ID

FROM    SYS.DM_DB_INDEX_USAGE_STATS

WHERE   DATABASE_ID = DB_ID(‘<DATABASENAME>’)

AND OBJECT_ID = OBJECT_ID(‘<TABLENAME>’)

Hope this helped you, Thanks for reading this article !

Read More

Microsoft SQL Server - Query to find the record counts of tables

Hi Everyone,

Use the below query to find the record count of tables.

SELECT

SCHEMA_NAME(T.SCHEMA_ID) AS SCHEMA_NAME,

T.NAME AS TABLE_NAME,

SCHEMA_NAME(T.SCHEMA_ID) + '.' + T.NAME AS FULL_TABLE_NAME,

I.ROWS

FROM

SYS.TABLES AS T INNER JOIN SYS.SYSINDEXES AS I

ON T.OBJECT_ID = I.ID AND I.INDID < 2

WHERE

SCHEMA_NAME(T.SCHEMA_ID)='DBO'

AND T.NAME IN ('Table1','Table2','Table3','TableN')

Thanks for reading my Blog !

Read More

Microsoft SQL Server - Query to fetch Table Meta Data

Use the below query to fetch table metadata in Microsoft SQL Server.

SELECT

TABLE_SCHEMA,

TABLE_NAME,

ORDINAL_POSITION,

COLUMN_NAME,

CASE DATA_TYPE

WHEN 'DATETIME' THEN UPPER(DATA_TYPE)

ELSE

UPPER(DATA_TYPE)+'('+LTRIM(RTRIM(DATA_LENGTH))+')'

END AS DATA_TYPE,

NULLABLE

FROM

(

SELECT

COLS.TABLE_SCHEMA,

COLS.TABLE_NAME,

COLS.COLUMN_NAME,

COLS.ORDINAL_POSITION,

COLS.DATA_TYPE,

CASE COLS.DATA_TYPE

  WHEN 'CHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'NCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'VARCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'NVARCHAR' THEN CONVERT(CHAR,COLS.CHARACTER_MAXIMUM_LENGTH)

  WHEN 'SMALLINT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'INT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'BIGINT' THEN CONVERT(CHAR,COLS.NUMERIC_PRECISION)

  WHEN 'DECIMAL' THEN RTRIM(CONVERT(CHAR,COLS.NUMERIC_PRECISION)) + ',' +RTRIM(CONVERT(CHAR,COLS.NUMERIC_SCALE))

ELSE

NULL

END AS DATA_LENGTH,

CASE COLS.IS_NULLABLE

WHEN 'NO' THEN 'NOT NULL'

WHEN 'YES' THEN 'NULL'

END AS NULLABLE

FROM

INFORMATION_SCHEMA.COLUMNS COLS

WHERE COLS.TABLE_SCHEMA = 'DBO'

AND COLS.TABLE_NAME = 'EMPLOYEE'

)A

ORDER BY  TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME

Can also use IN Clause for COLS.TABLE_NAME to include multiple tables.

 Thanks for reading my Blog !

Read More