Database/table information

This entry is part 2 of 2 in the series SQL tips and tricks

In the last few weeks, I improved/corrected lots of my existing stored procedures for my current project. It feels good knowing that my understanding of inline views has definitely improved. I’ve additionally learned a few SQL queries that provide me with database/table information.

I also faced a few issues while trying to use views in SQL Server. I have a few views in SQL Server that I can’t actually use because as soon as I save them, the content gets automatically formatted and changed. I’d be fine with it if the change didn’t influence the outcome, but it does. When I have several joins and then a WHERE clause, SQL Server always changes it to joins with an AND instead of the WHERE. That’s really annoying since the outcome with the AND is wrong. I hope I can figure out what’s going on soon but till then, I’ll end up saving those queries in a stored procedure directly (where it doesn’t get auto-formatted and changed).

A few things I’ve learned lately:

SQL Servers sys.sql_modules

In my current project, I ended up having quite a lot of stored procedures. I started getting a bit overwhelmed. For example, I knew I had used certain keywords (like TRANSACTION) in some procedures, but how could I quickly find them. Going through each stored procedure is an option, but a very slow, annoying, and frustrating one. Then I found out about the sys.sql_modules table. It has a ‘description’ column which contains SQL scripts for creating each existing view and stored procedure.

I started using following search

SELECT definition FROM sys.sql_modules 
WHERE definition LIKE '%TRANSACTION%'; 

That helped me tremendously in finding certain stored procedures very quickly.

SQL Servers sys.objects

The SQL query above helps in finding the wanted stored procedure/view since each ‘description’ column contains the name of the stored procedure/view within a few words. But I figured it would be nicer if I could just get the stored procedure/view name instead. And that’s where the sys.objects table comes handy.

SELECT DISTINCT o.name FROM sys.sql_modules sm 
INNER JOIN sys.objects o ON sm.object_id = o.object_id
WHERE sm.definition LIKE '%account%';

Other useful SQL queries to learn more about the database

The more tables/columns/views/stored procedures/functions/etc., the harder it gets to maintain the database and to keep an overview.

I found querying the Information Schema views very useful. For example, if you want to quickly know the columns of a table or view, use following query:

SELECT TABLE_NAME, COLUMN_NAME
FROM my_database.INFORMATION_SCHEMA.COLUMNS;
WHERE TABLE_NAME = 'my_table'

I’ve already mentioned how to search for certain keywords in views/stored procedures via sys.sql_modules. Another way of doing exactly this is via the Information Schema views. The following code snippet shows all CREATE VIEW/PROCEDURE functions for your database:

SELECT TABLE_NAME, VIEW_DEFINITION
FROM my_table.INFORMATION_SCHEMA.VIEWS;
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM my_table.INFORMATION_SCHEMA.ROUTINES;

Example usage

During development, I often have more than one database of the same thing. I have the main database which will eventually be deployed and which I use for all kind of tests. Then I have for each person that plays around with my software (for testing purposes or other) an additional copied database.

During development, I often need to correct some views or/and stored procedures which I update on my database and then retest. As soon as I’m happy with the changes, I need to also make my ‘testers’ happy and provide them with the latest version. I wrote a little script extra for that purpose to make updating the other database views/stored procedures so much easier and quicker.

use tester_x_db;
DECLARE @row int = 1;
DECLARE @sp varchar(8000);
DECLARE @maxViews int = 
    (SELECT COUNT(*) FROM my_db.INFORMATION_SCHEMA.VIEWS);
WHILE @row <= @maxViews
BEGIN
    SELECT @sp=REPLACE(VIEW_DEFINITION, 'CREATE', 'ALTER') FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY TABLE_NAME ASC) AS 
            rownumber,VIEW_DEFINITION
	    FROM my_db.INFORMATION_SCHEMA.VIEWS
	) AS tmp
    WHERE rownumber = @row;
    EXEC (@sp);
    SET @row = @row + 1;
END

DECLARE @maxRoutines int = 
    (SELECT COUNT(*) FROM my_db.INFORMATION_SCHEMA.ROUTINES);
SET @row = 1;
WHILE @row <= @maxRoutines
BEGIN
    SELECT @sp=REPLACE(ROUTINE_DEFINITION, 'CREATE', 'ALTER') FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY ROUTINE_NAME ASC) AS 
            rownumber,ROUTINE_DEFINITION
	    FROM my_db.INFORMATION_SCHEMA.ROUTINES
	 ) AS foo
    WHERE rownumber = @row;
    EXEC (@sp);
    SET @row = @row + 1;
END
Series Navigation<< SQL features

Leave a Reply

Your email address will not be published. Required fields are marked *