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!!!
To determine SQL Server version we need to run following commandGo@@VersionGO Enjoy !!!
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 !!!
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 !!!
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 …
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 …
To determine table detail like column name & data types for each individual table ,Index, primary key, foreign keys etc, sp_help is one of the best way. Syntax of sp_help is very simple as shown below sp_help tableName GO Instead of writing sp_help tablename you can get same information by highlighting table and press ALT+ …