| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2007-03-15 : 11:24:55
|
| Where can I see system tables in SSMS?------------------------I think, therefore I am - Rene Descartes |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-03-15 : 11:33:09
|
| Database->Tables->system tablesor is there something else you want?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-03-15 : 11:39:16
|
Causing trouble again?! Thank you spirit1!I checked Database->Tables->system tables, but there are very less tables here. I found lot of old system tables have become system views, DB->system databases -> master -> views->system views Where does the actual system data gets stored? I mean from where do these system views get the data?------------------------I think, therefore I am - Rene Descartes |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-03-15 : 11:47:10
|
| read this on where they are:http://weblogs.sqlteam.com/mladenp/archive/2007/03/12/60132.aspxyou can also use:SELECT object_definition(OBJECT_ID('sys.dm_db_index_physical_stats'))where you put in the object name and schema and you get the body of the object.be sure to put the result in text mode to see it right._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-03-15 : 12:01:54
|
| Thank you spirit1!Solved my doubt. One more question what is the difference between using SP_HELP/SP_HELPTEXT and object_definition?------------------------I think, therefore I am - Rene Descartes |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-03-15 : 12:54:20
|
| run object_definition on sp_helptext and you'll see _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-15 : 14:10:27
|
quote:
read this on where they are:http://weblogs.sqlteam.com/mladenp/archive/2007/03/12/60132.aspx
Note that the system tables are not in MsSqlSystemResource. The system stored procs, views, and functions are in there, but there are still system tables in each database just like they used to be - however, they are now truly hidden - SQL Server will not show them to you at all. You can see the data that is in them through the compatibility views (like sysobjects), which are there purely for backwards compatibility, or through the new catalog views (like sys.objects), which are how you should now query the System Catalog.BTW - The reason that system procs, views and functions are in MsSqlSystemResource is so that applying service packs is much simpler. In the past it would take longer to apply a service pack than it took to install SQL Server because the service pack would have to delete all the procs, views and functions and then recreate them in master. Now applying a service pack just replaces MsSqlSystemResource.See http://msdn2.microsoft.com/en-us/library/ms189082.aspx |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-03-15 : 15:28:26
|
| Great info! Thank you snSQL!------------------------I think, therefore I am - Rene Descartes |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-03-15 : 15:33:43
|
quote:
Originally posted by spirit1
run object_definition on sp_helptext and you'll see
Spirit1, I tried the follpwing SQL, SELECT object_definition (OBJECT_ID('sp_helptext'))GO sp_helptext sp_helptext GOBoth aare giving the same o/p!------------------------I think, therefore I am - Rene Descartes |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-03-16 : 05:48:02
|
| yes and both are reading from sys.syscomments._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2007-03-16 : 09:26:11
|
| Okay!Somehow I feel that there is more it than that. But anyhow I don't want to go deep. Thank you Spirit1!------------------------I think, therefore I am - Rene Descartes |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-03-16 : 13:20:41
|
quote:
Originally posted by spirit1
yes and both are reading from sys.syscomments.
But note that sys.syscomments is a compatibility view, the new view that you should use is sys.sql_modules. |
|
|
|
|
System tables in SSMS