Lately, I needed to develop a utility checking the structure of a SQL Serve 2005 database and saving structure and data in XML files. The objective was to easily obtain XML files to initialize the state of our database for Integration Testing.
Anyway, I needed to google a lot to find the SQL queries I needed to get all the details about the tables and structure of my database. Below, you’ll find the queries I used. I think it’s useful to store them in a single location.
To list all the tables (out of system tables) of your database: exec sp_tables @table_type=’TABLE’
To get all the details of a table (in several result sets): sp_help table_name
To list all columns from a table: select name from syscolumns where id=object_id(‘tablename’)”
To list the columns part of primary key:
SELECT cu.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu WHERE EXISTS ( SELECT tc.* FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc WHERE tc.CONSTRAINT_CATALOG = ‘databasename’ AND tc.TABLE_NAME = ‘tablename” AND tc.CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME )
check if a column is identity:SELECT COLUMNPROPERTY( OBJECT_ID(‘tablename’),’columnname’,'IsIdentity’)
I’m pretty sure there are tons of other useful commands that I still need to learn. But at least this was what I needed.
