Summary
If you’re an old-hat with databases, this blog post will not be new to you. I’m going to show some capabilities of SQL Server that will reveal how powerful a database server can be. These “secrets” (OK, they’re really not secret to anybody with a browser and Google) actually apply to other data servers as well.
SQL Has a Database of Your Databases
That’s right. You can open up the master database (under “System Databases”) and inside the “Views -> System Views” you’ll see a whole bunch of views to all the databases in the server. You can query things like the list of tables in your database:
SELECT * FROM yourdatabasename.INFORMATION_SCHEMA.TABLES
Both views and tables will be listed here. If you want to know the columns in your tables you can query from this view:
SELECT * FROM yourdatabasename.INFORMATION_SCHEMA.COLUMNS
This view will tell you which field is nullable, what the data type is, the character length, etc.
You can list all stored procedures with this query:
SELECT * FROM yourdatabasename.information_schema.routines WHERE routine_type = 'PROCEDURE'
You’ll need to use another query if you want the full text of the stored procedure:
USE [yourdatabasename]
SELECT OBJECT_DEFINITION(OBJECT_ID('stored_proc_name')) AS code
You might think you can use the ROUTINE_DEFINITION field from the previous query, but it has a limited string size and will cut off long stored procedure code.
Here is how you can find a list of primary keys, foreign keys and unique keys:
SELECT * FROM yourdatabasename.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
You should see something like this:
The constraint type field will tell you which constraint you are looking at but this table will not tell you which table is connected to any foreign keys. For that information you’ll have to run this query (the holy grail of foreign key queries):
SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
-- Force the column to be non-nullable (see SQL BU 325751)
--KEY_SEQ = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),
UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsUpdateCascade')
WHEN 1 THEN 0
ELSE 1
END),
DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,'CnstIsDeleteCascade')
WHEN 1 THEN 0
ELSE 1
END),
FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
PK_NAME = CONVERT(SYSNAME,I.NAME),
DEFERRABILITY = CONVERT(SMALLINT,7) -- SQL_NOT_DEFERRABLE
FROM SYS.ALL_OBJECTS O1,
SYS.ALL_OBJECTS O2,
SYS.ALL_COLUMNS C1,
SYS.ALL_COLUMNS C2,
SYS.FOREIGN_KEYS F
INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
INNER JOIN SYS.INDEXES I
ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
AND F.KEY_INDEX_ID = I.INDEX_ID)
WHERE O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
AND C2.COLUMN_ID = K.PARENT_COLUMN_ID
which can be found on this blog post: Identify all of your foreign keys in a SQL Server database.
This query can be used to generate foreign keys through code. The code necessary to create a foreign key looks like this:
ALTER TABLE product ADD CONSTRAINT FK_ProductType FOREIGN KEY (producttype) REFERENCES producttype(Id)
You’ll need variables for the two tables, the foreign key name and the two fields (producttype and id).
There are other views in the master database you can use to lookup your database configuration. Just poke around and see what these views reveal.
What’s the Purpose?
This information can be used to create a utility to compare the configuration of two databases. For example, you might have a production database (or customer facing database) and a development database. Many times these databases can get out of sync with each other. A utility can be created to match tables, stored procedures, foreign keys, indexes, etc. to produce a report of differences. By extension, a utility can be created to match one database to the other. For instance, you might want to synchronize your stored procedures. Or you might want to create a master template database and use it to generate indexes and foreign keys on your production and development databases.
I’ve used this information to create my ORM mapping code for Fluent NHibernate. You could create another format to store your database configuration (i.e. store your tables, indexes and foreign keys in an excel spreadsheet, or xml or json, etc.). This would be your master template that you can use to generate multiple databases. In turn, this generating capability can be used in an installer to generate a clean start-up database that you can then pre-populate with your static information.
As I mentioned earlier, this is not limited to SQL Server. You can store your database template in a format that is independent of the database you want to generate, then create multiple programs to generate the database type you want to use (like an Oracle database generator using the same tables, indexes and foreign key constraints).
One other application is to store your database configuration information in a format that can be easily copied and changed (i.e. like excel or text file format). Then you can keep multiple versions of your database handy. This can be linked with your software version so you can specify which database is needed with your latest software and a verification of your data structures can be executed by your deployment/install program before the software is upgraded.