Find Row Count in Table – Find Largest Table in Database – T-SQL

This script inform the number of rows are existing in the table. This script will gives row numbers for every tables in same database.

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC

Find Last intex value in sql

Declare @report_file varchar(50)
Set @report_file = 'murli@indigo.co.in'
Declare @rpt_name varchar(50)
--Set @rpt_name = Substring(@report_file,1,(DATALENGTH(@report_file)-CHARINDEX('.',REVERSE(@report_file))))
Set @rpt_name = Substring(@report_file,1,(DATALENGTH(@report_file)-CHARINDEX('.',REVERSE(@report_file))))
Print @rpt_name

Backup and Restore Database Via Query

Now, lets get the database backup from query

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\MultiFile\AdventureWorks.bak',

Now, let us see how we can split one database into two different database files. This method is very similar to taking a single-file backup. By simply adding an additional DISK option we can split the files backup files.

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'

Finaly restore the backup files in your database

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\MultiFile\AdventureWorks.bak',


Now let us see an example where we restore a database from a split file. This method is very similar to restoring a database from a single file; just add an additional DISK option.

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK = 'C:\Backup\MultiFile\AdventureWorks3.bak'
WITH REPLACE

Create Temp table and temp varible table

-- Create Temp Table and insert single row
CREATE TABLE #TempTable (Col1 VARCHAR(100))
INSERT INTO #TempTable (Col1) VALUES('Temp Table - Outside Tran');
-- Create Table Variable and insert single row
DECLARE @TableVar TABLE(Col1 VARCHAR(100))
INSERT INTO @TableVar (Col1) VALUES('Table Var - Outside Tran');
-- Check the Values in tables
SELECT Col1 AS TempTable_BeforeTransaction FROM #TempTable;
SELECT Col1 AS TableVar_BeforeTransaction FROM @TableVar;

Find the any number(sec, third ,sec last) Top Row or bottom row using sql.

This Example for find 4th row

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY MytblCOlumn_ID) AS RowNumber FROM tbl_Murli) as tbl_Murli WHERE RowNumber = 4

Calculate the execution time of query

DECLARE @start datetime
SELECT @start = getdate()
BEGIN TRANSACTION
-- Stuff to test comes here.
--Select * from tbltest
Declare @int int
Set @int = 0
Print @start
While(@int < 200000)
Begin
Print @int;
Set @int = @int +1
End
--PRINT 'It took ' + ltrim(str(datediff(ss, @start, getdate()))) + ' sec.'
Select Ltrim(STR(DATEDIFF(SS,@Start,Getdate())))+ ' Sec'
ROLLBACK TRANSACTION

Add Delete modify the Column in Database

Before Add the Column in the table need to check column already exists or not

If Not Exists(select * from sys.columns where Name = N'ArabicName' and Object_ID = Object_ID(N'Branch'))
Alter Table [Branch] Add ArabicName nvarchar(500)
Go

Drop the Coloumn from the table

ALTER TABLE tbl1 Drop Column abcd;


Modify the coulmn size from the existing table
ALTER TABLE tbl1 Alter Column abcd nvarchar(4000) not null;