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;
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