A part from performance many times we faced challenges related to space of our database. Sometimes our database is actually taking huge space and sometimes it our mistake due to which it took space. It is worst condition when you are on a dedicated hosting or cloud hosting with limited space. In such case “Compression” …
This is very interesting feature and I recently come to know this awesome feature. Lets understand this by an example below Suppose we have a student table with following structure as shown in below image We have a detail column with VARCHAR(MAX) . Now as it is VARCHAR(MAX) column it may content a …
In last few tips TIP #64 & TIP #65 we get information how to take backup & How to ensure it can be restore? Now lets me share some more information related to backup. Problem:- Sometimes , we are interested to know when last backup is taken for particular database ? Solution: The first …
As we discussed yesterday a hidden feature tip #51 which is sp_MSForeachtable . sp_MsForeachtable is useful to run command on each table of selected database. Now what if we want to run command on each database also ? then in that case we need to take help of sp_MsForeachDB. Sp_MsForEachDB by the name it …
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] = …
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 …
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) …