SQL TIps

Determine current date with different option TIP #16

We can determine date with different option in SQL server Option 1:– with GETDATE() we can get current date & time Go  SELECT GETDATE()GO Option 2:- With sysDateTime()  , we can get current date & time (with nano seconds)GO   SELECT SYSDATETIME()GOOption 3:- with SYSDATETIMEOFFSET() , We can get current date & time with timezone offsetGo  …

How to determine when Last backup taken of a database n SQL Server? TIP #15

Sometime we require to know when we have taken last backup. So let me share here all the backup, log shipping related detail you can find in the MSDB database which is system database.SQL server maintain all the backup related detail in the MSDB database table As Show in the figure Now we can run …

How to update statistics ? TIP #14

b In last TIP tip#13, We learn how to find last updated statistics status. Now we know when it last updated so it may be require we need to update statistics for some of table. So to update statistics we need to write following command ( if we want to update  statistics of entire tables …

How to determine Last statistics update? TIP #13

To determine last statistics update we need to run following command. GoSELECT o.name, i.name AS [Index Name],         STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],        s.auto_created, s.no_recompute, s.user_createdFROM sys.objects AS o WITH (NOLOCK)INNER JOIN sys.indexes AS i WITH (NOLOCK)ON o.[object_id] = i.[object_id]INNER JOIN sys.stats AS s WITH (NOLOCK)ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_idWHERE o.[type] …

How to find what is currently running on your SQL SERVER? TIP # 12

When we are keen to know that what is currently running on SQL SERVER for analysis purpose then with the help of below SQL statement we can easily find those statements, Stored procedure, status etc. GoSELECT [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid), [User] = nt_username, [Status] = er.status, [Wait] = wait_type, [Individual Query] = …

How to set database as Single User TIP #10

Sometimes it is require to  set database as single user for maintenance or other recovery purpose. We need to write following statement GO ALTER DATABASE FriendsDBSET SINGLE_USER  WITH  ROLLBACK IMMEDIATE; GO Here ROLLBACK IMMEDIATE  means if any transaction going on by other user then Rollback that data. See below snap for more detail Enjoy !!!

How to determine table reference /used in stored procedure- TIP #9

To determine table used in stored procedure or table reference We need to run following commandGoSELECT object_Name(Id)as objectName,text FROM syscomments  WHERE text like ‘%tblUser%’GoWe can use following command as wellGoSELECT ROUTINE_NAME,ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION like ‘%tblUser%’ GO In the above queries we have used tblUser as table name we can replace it and add any …