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 …

Get files (MDF/LDF) information of all databases – TIP #6

To get file (MDF/LDF) information we can write following query GOEXEC sp_MSforeachdb ‘USE ?  Execute sp_helpFile’ GO Or we can write following commandGoEXEC sp_MSforeachdb ‘USE ? SELECT ”?”, SF.filename, SF.size FROM sys.sysfiles SF’Go Or we can write following commandGO SELECT Name,Physical_name As current_file_LocationFrom sys.master_Files Go Enjoy !!!

Get all stored procedures name from Database -Tip #5

To get all stored procedures from database then we can use following commandGOSELECT name,type_Desc,create_Date,Modify_DateFROM sys.procedures WHERE type=’p’AND type_desc =’SQL_STORED_PROCEDURE’GO Or we can use following commandGOSELECT ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_DEFINITION,CREATED,LAST_ALTEREDFROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_TYPE =’PROCEDURE’ Enjoy  !!!

GET all columns of a table–TIP #4

  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 …

Get all the tables name from a database –TIP #3

We can get all the table’s name from a database using following simple syntax GOSELECT * FROM sys.objects WHERE type=’U’ AND Name <> ‘sysdiagrams’GO or we can use following commandGOSELECT * FROM sys.tablesGO The result of above statement is same Or we can use one more command which is as follow SELECT * FROM INFORMATION_SCHEMA.TABLES …