SELECT-ing About Your Database

I may be switching up the title, but this is really a Part 4 of my closer look at SELECTs.

(Miss my previous posts about SELECTs? Check out Part 1, Part 2 and Part 3!)

Up to now, I’ve talked about SELECTs for your database tables and objects and they can start to help you understand how you use your data. But now let’s build on the other things you can SELECT from to learn about your database.

There’s a lot of meta data (aka data about data) about your database, and about your SQL Server instance for that matter, that you can get from various SELECT statements.

The first objects that help us in these are the system object tables – well, technically they’re views. The ones most people commonly look at are the sys schema. You can find a list of the ones available here.

I’d also like to mention the INFORMATION_SCHEMA views, which I think get overlooked a lot. To be honest, I usually go straight to sys.objects and the other related system objects but INFORMATION_SCHEMA views also present much of the same information in a different format. (And they’re ANSI compatible too for those of you have to work with multiple database systems so you may want to take advantage of them. Not all use them so double check first.)

Here are some of the views from both the sys and INFORMATION_SCHEMA schemas that I use fairly often:

• sys.objects (all objects in the database)
• sys.columns (all the columns from tables, views, functions, etc.)
• sys.indexes (all the indexes)
• sys.sql_modules (definitions for objects which can have definitions – stored procs, views, etc.)
• INFORMATION_SCHEMA.TABLES (all tables and views)
• INFORMATION_SCHEMA.COLUMNS (all columns)
• INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS (foreign keys)
• INFORMATION_SCHEMA.KEY_COLUMN_USAGE (columns used in constraints)

These are great for telling you about what’s in your database and seeing what the different objects are. Read up on these objects (see the links above) and you can figure out how you can use these. For example, if I have to make a change to a table and I want to find out which objects reference that table, I can just run the following query:

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE definition LIKE '%MyObjectName%'
ORDER BY OBJECT_NAME(object_id)

I often use these objects if I want to check to see if a column is on the table before I try to add it for a deployment script:

IF NOT EXISTS (
 SELECT *
 FROM sys.objects o
 JOIN sys.columns c ON o.object_id = c.object_id
 WHERE o.name = 'table_name'
 AND c.name = 'new_column'
 )
ALTER TABLE table_name
ADD new_column varchar(10) NULL
GO

Or I could write that same statement this way:

IF NOT EXISTS (
 SELECT *
 FROM INFORMATION_SCHEMA.columns
 WHERE table_name = 'table_name'
 AND column_name = 'new_column'
 )
ALTER TABLE table_name
ADD new_column varchar(10) NULL
GO

I use these types of statements all the time. I often have to use the same scripts against databases which may or may not have been altered for the new schema so these help me avoid error messages and ensure that the database schema will look the way I expect it to at the end.

For those who are still learning T-SQL, the one thing I will point out here is where the SELECT is as part of entire statement. We’re not just selecting the data but we’re using the existence of a result set to determine whether or not to run the next statement. I’m sure I will have more to say about this at some point in the future because there are definitely performance implications with this.

Having the ability to access the meta data about our database through SELECT statements allows us to do more with our database. The immediate benefit is seeing if the database is actually seeing what’s there to help us to figure out if it is set up the way we designed it to be. But this is only a starting point.

 

Advertisement