Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Querying System Tables Expand / Collapse
Author
Message
Posted Friday, May 27, 2005 5:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 8, 2013 7:23 AM
Points: 199, Visits: 136
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/queryingsystemtables.asp
Post #185857
Posted Tuesday, June 14, 2005 4:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 8, 2013 5:22 AM
Points: 119, Visits: 9
A great way of finding out more about system tables is to look at the system stored procedures in the master database, whenever I need to find out something about the db metadata that isnt commonly known this is the place to start. sp_help for example is a great place to look for the the things mentioned in the article.

btw the I think the identity column is identified by (status & 128) = 128 as its a bitwise column.

If you need to look at undocumented functionality trace while doing what you want to do in enterprise manager then take a closer look at the releavant sps, obviously be cautious about using unsupported system sps, columns etc!



Phil Nicholas
Post #190255
Posted Wednesday, August 31, 2005 9:10 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 6, 2005 10:05 AM
Points: 2, Visits: 1

A good summary to all the system tables ... I feel if some one master these tables he/she will get more out of SQL Server ...

Regards
MD




Hi! Its - Manu Dutt
Visit me at - manudutt.co.nr
Post #215732
Posted Thursday, September 1, 2005 2:02 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 1, 2005 1:43 PM
Points: 19, Visits: 1

just a comment, in the syscolumns table there's another way to get the names and easier for me to remember

instead of querying the table as you do, you could also use the OBJECT_ID function wich does the same as the subquery you use there

SELECT name FROM syscolumns WHERE ID = OBJECT_ID('XYZ')




Kindest Regards from Mexico,

Jose Torres
Post #216328
Posted Friday, October 20, 2006 6:31 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 31, 2007 8:22 AM
Points: 92, Visits: 1

Great article! I will keep it as a reference.

In a future article, you could say something about the views in the master database, such as tables, columns, etc.

Post #316895
Posted Friday, October 20, 2006 7:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 17, 2014 9:13 AM
Points: 209, Visits: 377

I appreciate articles like this; even though I've been a DBA for a while I almost always see something in this type of article that I didn't know.  I think it's always good to go back and revisit things we learned when we first started as a DBA.  Also, this type of article is a great tool to give to junior DBAs trying to find introductory SQL Server information.  I'm currently bringing on a junior DBA and I gave him the link to this article; he WILL read it.  LOL

Mark

Post #316914
Posted Friday, October 20, 2006 10:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 11:20 AM
Points: 263, Visits: 275
Very good article!

I thought I would share another good resource on this topic. Microsoft has a system table map available on their website.

http://www.microsoft.com/sql/prodinfo/previousversions/systables.mspx

-Robert


SQL guy and Houston Magician
Post #317010
Posted Friday, October 20, 2006 10:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 5, 2010 5:40 AM
Points: 12, Visits: 5

Good article. 

I find using the system table invaluable.  I thought I would share a simple query of the system tables that I use to find tables and columns where I supply a portion of what I might think a column name would contain.  I find this quite useful when you have a large, complex database and you don't know exactly which tables you may want to look in...much easier than using Enterprise Manager.

DECLARE @Column sysname
SELECT @Column = UPPER ('%STRING%') 
-- Replace 'STRING' with the portion of the column name you wish to search for
--
SELECT sysobjects.name AS Table_name, sysobjects.xtype, syscolumns.name AS Column_name, systypes.name AS Column_Type, syscolumns.length AS Column_length, systypes.allownulls AS Column_nullable
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype = systypes.xusertype
WHERE  UPPER (syscolumns.name) LIKE @Column
-- choose to limit to just tables, just views, or both by commenting out the ones you don't want
 AND sysobjects.xtype = 'U' -- tables only
-- AND sysobjects.xtype = 'V' -- views
-- AND sysobjects.xtype IN ('U', 'V') -- tables and views
ORDER BY sysobjects.type, sysobjects.name

Post #317022
Posted Friday, October 20, 2006 11:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, July 27, 2013 8:41 PM
Points: 241, Visits: 168

While the information you are getting is invaluable --why make systables your first stop?  Information_schema views are much more user-friendly. (Table_name vs object_name(id)). And they upgrade better.

Only bugger with Information_Schema views -- Indexes are unavailable and you must resort to a function or sysindexes.




Post #317043
Posted Tuesday, October 24, 2006 2:28 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, October 20, 2014 12:09 PM
Points: 91, Visits: 198

Very good post Vasant, and I also appreciate John Robinson's script for finding tables or columns from a portion of the name.

After several years of using SQL 2000, I stumbled across the fact that the functionality of John's script, searching for an object by a partial name, is built into Query Analyzer!

Just click on the menu: Tools|Object Search|New - this brings up a dialog with lots of options - you can search for various types of objects (tables, columns, views, etc) in your choice of database or across all the databases on the server.

 As my contribution to this thread, here is a quick-and-dirty way of getting table row counts for a database:

SELECT
 so.[name] tableName
, si.rowcnt [RowCount]
FROM sysindexes si
JOIN sysobjects so ON si.id = so.id
WHERE
 si.indid IN(1,0)
AND so.xtype = 'U'

Of course, you can ORDER BY so.[name] to alphabetize the result or by si.rowcnt to order by number of rows, and so on.

Just be aware that if the Statistics for the indexes are not up-to-date, the rowcounts won't be correct - in fact, they could be way off. But for a lot of situations, this little script it good enough, and it's MUCH faster than looping through the tables and doing a SELECT count(*) FROM TableName for each.

Best regards,
SteveR

 




Post #317713
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse