To open SQL Profiler (right now I am talking about SQL Server 2012) you need to write Profiler.exe on run command as shown in below figure for earlier version you can use profiler90.exe. You can find various parameters option using Profiler.exe –? command When you run this command you will find below screen. Although …
Although, I did not get chance to use this facility in my projects but it is good to know feature. Suppose you want looping and want to insert data in table then in such situation you can use this GO lopping feature. In below example We are inserting data into debug table with GO statement …
Sometimes, we need to understand what exactly going on with particular statement, stored procedure why it is slow? To check performance of statement what exactly going on we also interested how many logical reads, physical reads a particular statement taking. To achieve this we need to write SET STATISTICS IO ON For exampleGOSET STATISTICS IO …
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 …
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 …
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!!!
To determine SQL Server version we need to run following commandGo@@VersionGO Enjoy !!!
To Get all columns information of a table we can use following command GO SELECT st.name, sc.name,sc.is_identity AS IdentityColumn, sc.is_nullable AS NullableColumn,sc.max_length as colmaxlength,sty.name as columnTypeFROM sys.columns scINNER JOIN sys.tables st ON st.object_id = sc.object_idINNER JOIN systypes sty on sty.xtype = sc.system_type_id ORDER By st.name,sc.nameGO Or we can use following statement SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE FROM …