Madhivanan

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

select
        table_name
from 
        INFORMATION_SCHEMA.TABLES
where 
        objectproperty(object_id(table_name),'TableHasForeignKey')=0 AND 
        objectproperty(object_id(table_name),'TableHasPrimaryKey')=0 AND 
        table_type='BASE TABLE'
order by 
        table_name

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='your_database'
group by database_name

select destination_database_name,max(restore_date) as restore_date from msdb..restorehistory
where destination_database_name='your_database'
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?