Normalisation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Normalisation

Author

Topic

Lorna70
Starting Member

19 Posts

Posted - 2010-05-31 : 07:06:14

Hi

I need to normalise a database in SQL Server 2005 Express. There are lots of tables I don't think are being used but I need to know for sure before I delete them. Is there any way of finding out when these tables were last accessed? Also, there is a table with 80 fields - is there any way of knowing if these fields are still accessed?

Thanks
Lorna

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-31 : 07:39:40

You can IMHO only get the last access date from a table since a restart of the SQL Server.
That means if your SQL Server was restarted yesterday the the needed information isn't available.
Maybe someone will correct me if i am wrong.

Try this:


-- show us the "restartdate" of this server
SELECT datediff(dd,create_date,getdate()) as [Days since SQL-Restart], create_date as am
FROM sys.databases 
WHERE name = 'tempdb'

-- ##############################
   USE <your_db_name_here
   GO
   sp_updatestats 
-- ##############################


select 
table_catalog,
table_name,
last_user_seek,
last_user_scan,
last_user_update

from sys.dm_db_index_usage_stats i 
join INFORMATION_SCHEMA.TABLES t
  on t.TABLE_NAME = object_name(i.[object_id]) and 
     object_name(i.[object_id]) not like 'sys%' and
     object_name(i.[object_id]) not like 'ms%'

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources