April 2009 - Posts

Tables with no Primary and Foreign keys

John Paul Cook has posted in his blog to show the list of tables that have no Referential Integrity.

What if you want to know list of tables that have no primary and foreign keys?

Here is the query

        objectproperty(object_id(table_name),'TableHasForeignKey')=0 AND 
        objectproperty(object_id(table_name),'TableHasPrimaryKey')=0 AND 
order by 


Posted by Madhivanan with no comments

Last Backup/Restore date of database

If you want to know the last Backup/Restore date of database, use the following

--Last Backup date of database

--Method 1

In Management Studio,Expand database,Right Click on the database and click properties
Refer Last Batabase Backup which is availalbe under General

--Method 2

select database_name,max(backup_finish_date) as backup_finish_date from msdb..backupset
where database_name=
group by database_name

--Last Restore date of database

select destination_database_name,max(restore_date) as restore_date from msdb..restorehistory
where destination_database_name=
group by destination_database_name


Table level Backup/Restore

It may be very useful if we have feature to take backup or restore a table

I have posted my suggestion here

Table level Backup/Restore

What do you think of it?