Querying System Tables

  • Vasant Raj

    SSCommitted

    Points: 1835

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/queryingsystemtables.asp

  • Phil.Nicholas

    SSCrazy

    Points: 2067

    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

  • Manu Dutt

    Valued Member

    Points: 74

    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

  • Jose Torres

    SSC Veteran

    Points: 297

    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

  • Carlos Urbina

    SSCommitted

    Points: 1850

    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.

  • Mark Yelton

    Default port

    Points: 1490

    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

  • R Michael

    SSCarpal Tunnel

    Points: 4211

    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

  • John Robinson-146225

    SSC Rookie

    Points: 42

    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

  • michanne

    SSCrazy

    Points: 2329

    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.

  • Steve Rosenbach

    SSCrazy

    Points: 2071

    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

     

  • Matthew McClellan

    SSC Enthusiast

    Points: 132

    Good examples of using system tables.  I would use information_schema views except that not all of the information available via system tables exists in the views; and for that reason, I prefer to just be consistent and always use the system tables.

    I don't like the conclusion though - that it isn't necessary to save scripts but rather rely on a "source" database and use system tables to push objects to "target" databases.  While that might correctly copy the objects, the developer is left with no change history, no way of rolling back to previous versions, no way of commenting about changes, no way of grouping objects together logically and labeling them as a release, and the "target" database must be reachable from the "source" database.  I realize that it is more work to manage SQL Server objects as scripts, but I believe configuration management can be much more robust by doing so.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply