Some Useful Queries in Sql Server

I. Finding Last Backup Date in SQL Server 2005

select a.name as DBName, coalesce(convert(varchar(12),max(b.backup_finish_date),101),'Not Yet Taken') 
as LastBackupDate,
coalesce(convert(varchar(12),max(b.user_name),101),'NA') as Taken from sys.sysdatabases a
LEFT OUTER JOIN
msdb.dbo.backupset b 
on b.database_name=a.name
group by a.name
order by a.name 
--------------------------------------------

II. List Empty Tables in SQL Server

WITH EmptyRows AS 
( 
  SELECT SUM(row_count) AS [TotalRows], 
         OBJECT_NAME(OBJECT_ID) AS TableName 
  FROM sys.dm_db_partition_stats 
  WHERE index_id = 0 OR index_id = 1 
  GROUP BY OBJECT_ID 
) 
SELECT * FROM EmptyRows 
WHERE [TotalRows] = 0

----------------------------------------------

III. Find the Missing Identity Numbers in SQL Server 2005/2008

WITH Missing (missnum, maxid)
AS
(
SELECT 1 AS missnum, (select max(id) from DebitNote_Attachments)
UNION ALL
SELECT missnum + 1, maxid FROM Missing
WHERE missnum < maxid
)

SELECT missnum
FROM Missing
LEFT OUTER JOIN DebitNote_Attachments tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0) 

---------------------------------------------

IV. How to find columns count of any table in any database from sql server

SELECT COUNT(*) FROM .sys.columns
WHERE object_id = OBJECT_ID('..')

-- Ex : 

SELECT COUNT(*) COLUMNCOUNT FROM DBNAME.SYS.COLUMNS
WHERE OBJECT_ID =OBJECT_ID('DBNAME.DBO.TABLE_NAME')

----------------------------------------------

V. How to delete the top 100 row from a table using Sql Server?

WITH CTE AS
(
SELECT TOP 100 *
FROM TABLE_NAME
--ORDER BY a1
)
DELETE FROM CTE

-----------------------------------------------

VI. How to get the row count of all tables in a SQL SERVER

SELECT sc.name +'.'+ ta.name TableName, SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
   ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
   ON ta.schema_id = sc.schema_id
WHERE  
ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY 
sc.name,ta.name
ORDER BY 
SUM(pa.rows) DESC

-----------------------------------------------

VII. How to find duplicate records in a table ?

select * from Friends

select Fid, FName, count(Fid) Dups from Friends

group by Fid, FName

having count(Fid)>1

order by Fid

-----------------------------------------------------------------------------------------

VIII. How to generate Row Number in existing table SQL-SERVER

select * from Friends

To generate Row Number,

WITH CTE as (select row_number() over (order by Fid asc) as rowid, * from Friends)

select * from CTE

And we can delete the duplicate records from the table

WITH CTE as (select row_number() over (order by Fid asc) as rowid, * from Friends)

select * from CTE where Fid=2

WITH CTE as (select row_number() over (order by Fid asc) as rowid, * from Friends)

--select * from CTE where Fid=2 // comment this line ...

delete from CTE where rowid=3

WITH CTE as (select row_number() over (order by Fid asc) as rowid, * from Friends)

select * from CTE where Fid=2

--delete from CTE where rowid=3 // comment this line ...

-----------------------------------------------------------------------------------------