Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I restored a SQL Server 2000 database to 2005 then to 2012. I am not able to see system tables that existed in SQL Server 2000 now.

Can somebody please let me know what is the reason.

Thank you

share|improve this question
    
What system table are you trying to see, and why? –  Aaron Bertrand Jan 29 at 22:13
    
dbo.syscoloumns –  user3171149 Jan 29 at 22:16

1 Answer 1

up vote 6 down vote accepted

You should be using the catalog views instead of the old system tables (e.g. sys.objects instead of sys.sysobjects, sys.columns instead of sys.syscolumns). The system tables still exist, but they are there for backward compatibility only. Some of them have been moved to the hidden system database, mssqlsystemresource, and/or are only visible when connecting with the dedicated administrator connection. Of course you will need certain permissions to view the new catalog views.

Please read the notes on the old docs, e.g. for sys.syscolumns:

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Here is a link to the mapping document they mention, but don't link. This should show you how to start making the transition from, say, syscolumns to sys.columns.

Stop using the old system tables. Learn the new metadata; it's here to stay.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.