HOW TO VIEW SYSTEM TABLES IN SQL 2005
-
I WANT TO VIEW SYSTEM TABLES IN SQL 2005. IN SQL 2000, WHEN WE CREATE NEW DATABASE MANY SYSTEM TABLES ARE ALSO CREATED AUTOMATICALLY. BUT IN SQL 2005, I COULD'NT SEE ANY SYSTEM TABLES.
AND FROM WHICH SYSTEM TABLE I WILL GET COLUMNS/FIELDS DESCRIPTION OR COLUMN PROPERTIES IN A TABLE CREATED BY USER
PLEASE HELP ME
-
Try the INFORMATION_SCHEMA views.
--------------------
Colt 45 - the original point and click interface
-
sys.columns
-
In SQL Server 2005 many of the system views are hidden, and you cannot access them using a normal connection. The SQL Server 2000 system tables have been replaced with system views (with the same names) and there are many new system views.
To see all system tables/views accessible look at sys.all_objects and sys.all_columns.
If you want the definitions of system stored procedures and columns, then in the master database look at the sys.sql_modules. (the ones that start with sp_ will be available in all of your databases as if they were locally defined.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
SQL2005 has a complete new thing called schema's (already an old thing in Oracle.. ;-)) in which the tables aren't owned by a user anymore but are owned by a schema. All the system tables have been moved to the sys schema. If you use a sql2000 client to connect to a sql2005 db it doesn't know about the schema's. The enterprise manager won't connect to a sql2005 db, the query analyzer will but you can't use the object browser because it doesn't handle the schema's correctly.
In sql 2000 the sql statement "SELECT * FROM sysobjects WHERE xtype = 'u'" will get you a list of user objects, the same thing can be done in sql2005 by using
"SELECT * FROM sys.objects WHERE type = 'u'"
regards
Wim
Kindest Regards,Wim van den Brink
-
Wim is correct about the change to schemas (it is nice to see that MS is embracing another chunk of the SQL Standard
) The old system tables will continue to work for a while but will live under the sys schema (dbo.sysobjects is not sys.sysobjects). One should however move to the new system views (for sysobjects it is sys.objects).Another alternative, is once again from the SQL standard, and is supported by SQL Server, is the INFORMATION_SCHEMA schema. You can explore the database schema via the views in the INFORMATION_SCHEMA schema. E.g.:
select * from INFORMATION_SCHEMA.tables
The information will not be complete, but portable.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply