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 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 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+ …
To determine size of table in a database or size of database, we have simple syntax in SQL Server which is SP_SPACEUSED. Syntax is simple enough as shown below 1) If we want to determine size of the database then we have to write below statement and execute Use DatabaseName GO SP_SPACEUSED GO 2) …