I first learned about this table right as I was learning about being a DBA. No, not because this is a table that a new DBA would ever be thinking about. I learned about this table because we were migrating some databases from Sybase to SQL 2000 and we needed to scrub the stored procedures in order to find and replace text that we knew would not be compatible. In other words, we had built our own migration assistant.
And I was fortunate to have that experience because from that day forward I was always curious about what other details were inside of the system tables. I always assumed other DBAs were curious as well, but over time I have come to realize that nobody goes looking under the covers of SQL Server until they have a reason to do so. And that’s one of the reasons I started this whole series.
Before we get started please note that this system table will be removed from a future version of SQL Server.
What is this table for?
This system table is scoped at the database level (not the instance level), so be mindful of that when you are using syscomments. Run the following code:
SELECT * FROM master.dbo.syscomments
The BOL defines this table as:
“Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.”
Which made me wonder, what isn’t included in syscomments? Well, you can run this to find out for yourself:
SELECT type_desc FROM sys.objects WHERE object_id NOT IN ( SELECT id FROM syscomments) GROUP BY type_desc
And now we know the results for my instance of SQL 2008 R2:
FOREIGN_KEY_CONSTRAINT
INTERNAL_TABLE
PRIMARY_KEY_CONSTRAINT
SERVICE_QUEUE
SYSTEM_TABLE
USER_TABLE
Why should you care?
As I mentioned yesterday, you care about syscomments because the sysdepends table lies to you. So when you need to know more about the objects definitions inside your database you will want to check here first. Perhaps you wanted to know if you had any objects referencing a linked server? Well then, you could do this:
SELECT * FROM syscomments WHERE text LIKE '%linked_server_name_here%'
And now you would know which views would possibly be affected by a change being made to that particular linked server.
Where else is this information?
Remember how I said this system table will be removed from a future version of SQL Server? Good, then start using the sys.sql_modules object catalog view:
SELECT * FROM sys.sql_modules
Check out the column named “is_schema_bound”. If you have ever wanted to know what objects you have that are defined with the SCHEMABINDING option then all you need to do is run this:
SELECT * FROM sys.sql_modules WHERE is_schema_bound = 1
Which is a whole lot easier than having to parse out the word SCHEMABINDING from the syscomments, isn’t it? Of course it is. So when you want to find out more about the object dependencies in your database you should dig through the syscomments table and/or the sys.sql_modules object catalog view.
[UPDATED: I was notified by Aaron Bertrand (blog | @aaronbertrand) that syscomments will have multiple rows if your object definition has more than 4000 characters. So, if you build a dependency checker you should be mindful of this as you may need to return several rows in order to get the full definition.]
