Sometimes some small things which we ignore can impact more. By default when you run any statement you will see in the message like no of records affected.As shown in below figure. Although, most of the time we do not require such information but still it is overhead. So to stop such overhead which impact …
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 …
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 …
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 …
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] …
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] = …
As we mention in TIP 10 to set database user as Single user now to make this available again for all the user or multi user we need to do following ALTER DATABASE FriendsDBSET MULTI_USER WITH NO_WAIT; GO Here when we run above command FriendsDB again changed to MULTI USER. Here NO_WAIT means not …
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 !!!
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 …
To determine foreign key of the database we need to run following command GoSELECT NAME,OBJECT_NAME(Parent_Object_Id) As PrimaryTable,object_Name(Referenced_Object_Id) As ReferenceTable,create_Date,Modify_DateFROM sys.Foreign_KeysWHERE type =’F’GO Enjoy!!!