Skip to main content

Posts

Showing posts from January, 2017

SQL Server- How to get last access/update time for tables in Database?

One day I got requirement to find last updates of tables in database. Means I want to get all the tables’ last update in database and did surfing on google and found the best way. Link:https://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/
I am taking reference from above link and try to explain. See following query it may help you to find exact result.

SELECTOBJECT_NAME(OBJECT_ID)ASDatabaseName,last_user_update,* FROMsys.dm_db_index_usage_stats WHEREdatabase_id=DB_ID('<your_db_name>')

If you want to see the last update of particular table then use the following query.

SELECTOBJECT_NAME(OBJECT_ID)ASDatabaseName,last_user_update,* FROMsys.dm_db_index_usage_stats WHEREdatabase_id=DB_ID('<your_db_name>') ANDOBJECT_ID=OBJECT_ID('table_name')



SQL Server - How can we truncate all tables in Database?

sp_msforeachtable can be use to truncate tables from database. sp_msforeachtable is undocumented means this is not documented by Microsoft it can be change or modified at any time. It contains two parameters @command1 and @whereand using this procedure we can truncate tables.
If you want truncate all tables in database then use following query.

ExecSp_msforeachtable@command1='Truncate Table ?'

If you want to truncate all tables of particular schema then use following query

ExecSp_msforeachtable@command1='Truncate Table ?',@whereand='and Schema_Id=Schema_id(''schema_name'')'



SQL Server - How to find all empty/have values tables

Many times our managers ask to find empty tables and have value tables report. It is time taken to find the tables manually. I am writing some query which may help you to find empty and have values table. Following query help you to find allempty tablesin database.
1 –See following query

SELECTIST.TABLE_NAME , MAX(SI.rows ) TOTAL_RECORDS FROMsysindexesSI, INFORMATION_SCHEMA.TABLESIST WHEREIST.TABLE_NAME = OBJECT_NAME(SI.id)       AND IST.TABLE_TYPE = 'BASE TABLE' GROUPBYIST.TABLE_SCHEMA, IST.TABLE_NAME HAVINGMAX(SI.rows)<=0
2-If you want to find all empty tables of particular “schema” then use following query.

SELECTIST.TABLE_NAME , MAX(SI.rows ) TOTAL_RECORDS FROM

How to copy variable from one package to another SSIS package

We can easily copy and paste task, transformations, connection manager from one package to another, but like this, you cannot copy and paste variables from one to another package. If you have to use the same variable for another package it may take time to type variable’s name one by one.

One solution for this I am described below, we can copy and paste the variable from one package to another Package. Suppose we have a package pck1 and we need to copy all variable from package pck1 and paste to package pck2.


To copy the above variable, we have to open the XML code of pck1. How can we open the package in code mode? Please see the following image.
Copy the variable code shown as below.



Same as open package pck2 in code mode and replace variables attribute with copied code as below.

After replacing code and save, the package will look like as below.