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+ …
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) …