May 27, 2005 at 5:56 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rVasant/queryingsystemtables.asp
June 14, 2005 at 4:52 am
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
August 31, 2005 at 9:10 am
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
September 1, 2005 at 2:02 pm
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')
Jose Torres
October 20, 2006 at 6:31 am
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.
October 20, 2006 at 7:14 am
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
October 20, 2006 at 10:11 am
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
October 20, 2006 at 10:46 am
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
October 20, 2006 at 11:21 am
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.
October 24, 2006 at 2:28 pm
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
October 26, 2006 at 7:59 am
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply