Finding your way around an unfamiliar database

It can sometimes be difficult to find where various values are being stored in a database you are unfamiliar with, this is a T-SQL script I wrote some time ago when I was trying to find my way around a TechOne database and it came in handy back then and it recently came in handy once again when I was querying a Connectwise database.

What this script does is it cursors through all the tables and all the columns on those tables and counts the occurrences of the value you are searching for, and then gives you a result set listing which columns on which tables contain that value.

 

Warning: Think carefully before running this against your databases, this could cause performance issues.

The following query is very handy for finding which stored procedures, views & functions are referencing various values:

P = Stored procedure
V = View
TF = Table function
FN = Scalar function
AF = Aggregate function (CLR)
TR = SQL DML Trigger

You can find a full list of xtype values here: https://msdn.microsoft.com/en-AU/library/ms177596.aspx

Advertisements

Database Restructuring

A project I worked on a while ago where we were building a new web interface for an existing system which was originally built by another company, one of the requirements was that we use the existing database. Most of the tables in this database had primary key columns however there were no identity columns and they did not auto increment, this caused problems with our ORM framework when inserting new records, because the columns were not identity columns the LINQ to SQL could not get the the last inserted identity. The solution I came up with was to replace the existing primary key columns with new auto incrementing identity columns, this solution presented a number of challenges such as:

  • You can’t update identity columns so to get around this I move the data from each table into temporary tables, then drop the existing primary key column and create the new identity column and seed the new column to the max value of the old primary key. Then I can re-insert all the data with SET IDENTITY_INSERT ON.
  • Dropping the old primary key columns would violate foreign key constraints, therefore I cached the foreign key relationships in a temporary table then drop all constraints, once all the modifications have been made and all the data re-inserted I re-apply the foreign key constraints.

So to do this I wrote an SQL script which generates SQL to alter the database. First we load up all the names of the tables which are affected by this issue and setup some variables which will be used throughout the script:

DECLARE @tables TABLE(
	TABLE_NAME varchar(max)
)

-- get all the tables that need to be altered
INSERT @tables SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE COLUMN_NAME = 'row_key' 
			AND TABLE_NAME NOT IN ('table_1', 'table_2') 

We also want to exclude any tables that have the ‘row_key’ column but with auto increment, next we want to copy all the foreign key constraints so we can recreate them when we are finished:

DECLARE @foreign_keys TABLE(
	AlterStatement varchar(max),
	ConstraintName varchar(100),
	TableName varchar(100)
)

-- copy the relevent foreign key constraints
INSERT @foreign_keys SELECT  'ALTER TABLE ' + object_name(a.parent_object_id) +
		' ADD CONSTRAINT ' + a.name +
		' FOREIGN KEY (' + c.name + ') REFERENCES ' +
		object_name(b.referenced_object_id) +
		' (' + d.name + ')' as AlterStatement, 
		a.name as ConstraintName,
		object_name(a.parent_object_id) as TableName
	FROM    sys.foreign_keys a
			JOIN sys.foreign_key_columns b
				ON a.object_id=b.constraint_object_id
			JOIN sys.columns c
				ON b.parent_column_id = c.column_id AND a.parent_object_id=c.object_id
			JOIN sys.columns d
				ON b.referenced_column_id = d.column_id AND a.referenced_object_id = d.object_id
	WHERE   object_name(b.referenced_object_id) IN (SELECT TABLE_NAME FROM @tables)
	ORDER BY c.name

When then generate the drop constraints SQL and print it out:

SET @constraint_cursor = CURSOR FOR SELECT * FROM @foreign_keys

-- drop the foreign key constraints
OPEN @constraint_cursor

FETCH NEXT FROM @constraint_cursor INTO @alter_statement, @constraint_name, @constraint_table_name
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT 'ALTER TABLE [' + @constraint_table_name + '] DROP CONSTRAINT ' + @constraint_name
	PRINT 'GO'
	
	FETCH NEXT FROM @constraint_cursor INTO @alter_statement, @constraint_name, @constraint_table_name
END

CLOSE @constraint_cursor;
DEALLOCATE @constraint_cursor

We then want to loop through the tables with a cursor and this is where we do most of our work here and I’ve decided not to breakup this section of SQL because I thought it would be easier to read as one block of code, I’ve just added comments throughout:

SET @table_cursor = CURSOR FOR SELECT * FROM @tables;
OPEN @table_cursor;

PRINT 'EXEC sp_msforeachtable ''ALTER TABLE ? NOCHECK CONSTRAINT ALL'''
PRINT 'GO'
DECLARE @row_key_max int;
FETCH NEXT FROM @table_cursor INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
	-- get the max row_key of the current table
	DECLARE @ParmDefinition nvarchar(100) = '@row_key_max_out int OUTPUT';
	DECLARE @sql_to_the_max nvarchar(100) = 'SELECT @row_key_max_out = MAX(row_key) FROM [' + @table_name + ']'
	EXECUTE sp_executesql @sql_to_the_max, @ParmDefinition, @row_key_max_out=@row_key_max OUTPUT;
	
	-- get the primary key constraint
	DECLARE @pk_constraint_name varchar(100);
	SELECT @pk_constraint_name = Col.CONSTRAINT_NAME from 
		INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
		INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
			WHERE Col.Constraint_Name = Tab.Constraint_Name
				AND Col.Table_Name = Tab.Table_Name
				AND Constraint_Type = 'PRIMARY KEY '
				AND Col.Table_Name = @table_name
	
	IF @row_key_max IS NULL
		SET @row_key_max = 0;

	PRINT 'SP_RENAME ''[' + @table_name + '].row_key'', ''original_row_key'', ''COLUMN'';';
	PRINT 'GO'
	
	-- get default value constraint for original_row_key
	DECLARE @default_value_constrint varchar(100);
	SELECT @default_value_constrint = c.name FROM sys.all_columns a 
		INNER JOIN sys.tables b 
			ON a.object_id = b.object_id
		INNER JOIN sys.default_constraints c
			ON a.default_object_id = c.object_id
		WHERE b.name= @table_name
			AND a.name = 'row_key'
	
	IF @default_value_constrint IS NOT NULL
	BEGIN
		PRINT 'ALTER TABLE [' + @table_name + '] DROP CONSTRAINT ' + @default_value_constrint + ';' 
		PRINT 'GO'
	END
	
	IF @pk_constraint_name IS NOT NULL
	BEGIN
		PRINT 'ALTER TABLE [' + @table_name + '] DROP ' + @pk_constraint_name + ';' 
		PRINT 'GO'
	END
	
	-- disable all triggers and add the new column
	PRINT 'DISABLE TRIGGER ALL ON [' + @table_name + '];'
	PRINT 'GO'
	PRINT 'ALTER TABLE [' + @table_name + '] ADD row_key INT PRIMARY KEY IDENTITY(' + CAST((@row_key_max + 1) as varchar) + ',1) NOT NULL;'
	PRINT 'GO'

	-- cache all the data in another table and delete everything in the original table
	PRINT 'SELECT * INTO [' + @table_name + '_temp] FROM [' + @table_name + ']'
	PRINT 'GO'
	PRINT 'DELETE FROM [' + @table_name + ']' 
	PRINT 'GO'

	-- drop the original_row_key column and set IDENTITY_INSERT ON
	PRINT 'ALTER TABLE [' + @table_name + '] DROP COLUMN original_row_key'
	PRINT 'GO'
	PRINT 'SET IDENTITY_INSERT [' + @table_name + '] ON'
	
	-- get all the column names for the table
	DECLARE @columns varchar(max); 	
	SELECT @columns = COALESCE(@columns+', ', '') + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_NAME = @table_name AND COLUMN_NAME != 'original_row_key' AND COLUMN_NAME != 'row_key'
	
	-- get all the value column names for the table
	DECLARE @value_columns varchar(max);
	SELECT @value_columns = COALESCE(@value_columns+', ', '') + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_NAME = @table_name AND COLUMN_NAME != 'original_row_key' AND COLUMN_NAME != 'row_key'
	
	-- reinsert all the data back into the original table
	PRINT 'INSERT INTO [' + @table_name + '] (row_key, ' + @columns + ') SELECT original_row_key, ' + @value_columns + 
					' FROM [' + @table_name + '_temp]'
	-- turn off identity_insert and turn the check constraints back on
	PRINT 'SET IDENTITY_INSERT [' + @table_name + '] OFF'
	PRINT 'ALTER TABLE [' + @table_name + '] CHECK CONSTRAINT ALL;'
	PRINT 'GO'
	
	-- drop the _tmp table and re-enable triggers
	PRINT 'DROP TABLE [' + @table_name + '_temp]'
	PRINT 'GO'
	PRINT 'ENABLE TRIGGER ALL ON [' + @table_name + '];'
	PRINT 'GO'
	
	SET @columns = NULL;
	SET @value_columns = NULL;
	SET @constraint_name = NULL;
	SET @default_value_constrint = NULL;
	SET @pk_constraint_name = NULL;
	
	FETCH NEXT FROM @table_cursor INTO @table_name;
END
PRINT 'EXEC sp_msforeachtable ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'''
PRINT 'GO'

CLOSE @table_cursor;
DEALLOCATE @table_cursor;

Then the only thing left to do is re-apply the constraints and we are done:

-- re-apply the foreign key constraints

SET @constraint_cursor = CURSOR FOR SELECT * FROM @foreign_keys

OPEN @constraint_cursor

FETCH NEXT FROM @constraint_cursor INTO @alter_statement, @constraint_name, @constraint_table_name
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @alter_statement
	PRINT 'GO'
	
	FETCH NEXT FROM @constraint_cursor INTO @alter_statement, @constraint_name, @constraint_table_name
END

CLOSE @constraint_cursor;
DEALLOCATE @constraint_cursor 

Now you will need to do some testing to make sure the are no unwanted side effects from restructuring the database in this way, but in this instance for this system it worked perfectly.