List constraints for all tables in a database

I’ve been working on a project where I’m been responsible for creating a new relational database model. As the database model grew it became quite hard to get a clear picture over naming of constraints among other things, so I decided to create a script that listed all constraints using SQL Server Management Studio.

Below you can see my solution in how to accomplish this. Just remember to rename the MyCustomSchema to your schema name, or omit it if all your tables belong to the dbo schema.

 

DECLARE @table_name varchar(50)
DECLARE @current_table_name varchar(50)

DECLARE all_tables CURSOR  
FOR SELECT Name from sys.tables

OPEN all_tables

FETCH NEXT FROM all_tables INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @current_table_name = 'MyCustomSchema.' + @table_name
EXEC sp_helpconstraint @current_table_name
FETCH NEXT FROM all_tables INTO @table_name
END

CLOSE all_tables
DEALLOCATE all_tables

Working with comma separated strings in t-SQL

Problem

When working with t-SQL you might run into a scenario where you want to return all the records that corresponds to the IDs found in a comma separated list, e.g. ‘123,456,789’. There are several ways to solve this problem and while browsing around I found an interesting solution.

Solution

In the example below I take advantage of the CHARINDEX and PATINDEX functions in t-SQL to search for specific patterns in the comma-separated string by concatenating the customer ID with commas and then match the concatenated string with the id string. The functions returns a number  greater than 0 if the sought string exists in the ID string which can be useful in my scenario where I got a comma separated list with customer IDs.

CHARINDEX works similar to the Substring or InStr functions that you can find in other programming languages. The function takes three parameters and the first is the string to search for, the second is the string to search and the third (which is optional) is the starting point where the search will begin in the string to search.

PATINDEX works similar to CHARINDEX, but allows the use of wildcards when performing the search. The function takes two parameters and the first is the string or pattern to search for and the second is the column or variable that will be searched. The method returns an integer bigger.

The example below also show that LIKE can be used to search the comma-separated string for specific IDs using a concatenated value along with wildcards.

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE DATABASE [WeirdScience]
GO

USE [WeirdScience]
GO

CREATE TABLE [dbo].[Customers](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Customer] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO

INSERT Customers (Customer) VALUES (N'Dan Jansson')
INSERT Customers (Customer) VALUES (N'Lars Svensson')
INSERT Customers (Customer) VALUES (N'Lisa Karlsson')
INSERT Customers (Customer) VALUES (N'Kalle Johansson')
INSERT Customers (Customer) VALUES (N'Gullan Bengtsson')
GO

DECLARE @customerstring varchar(max);
SET @customerstring = '1,3,4,6'

-------------------------------
-- This example is based on the following article:
-- http://www.projectdmx.com/tsql/sqlarrays.aspx
-------------------------------

SELECT *
  FROM Customers
 WHERE CHARINDEX(
	',' + CAST(ID AS NVARCHAR(50)) + ',', 
	',' + @customerstring + ','
	) > 0
 
SELECT *
  FROM Customers 
 WHERE PATINDEX(
	'%,' + CAST(ID AS NVARCHAR(50)) + ',%', 
	',' + @customerstring + ','
	) > 0

SELECT *
  FROM Customers
 WHERE ',' + @customerstring + ',' 
  LIKE '%,' + CAST(ID AS NVARCHAR(50)) + ',%'

USE master
GO

DROP DATABASE WeirdScience
GO

For more ideas on how to work with arrays in SQL Server you should take a look at the excellent article found here: http://www.projectdmx.com/tsql/sqlarrays.aspx

Shrinking the Transaction Log in SQL Server

Problem

Well, this is a classical problem: The transaction log has been growing since who knows when and is now taking up almost all space on the server. You need to shrink the transaction log in order to free space on the server.

Solution

There are several solutions to this problem and it all depends on your database. This time I used the following script to get rid of all data in the transaction log and worth mentioning is that I didn’t care about the data in the transaction log; my goal was only to get rid of the unnecessary data and to reduce the size of the transaction log.

USE [MyDatabase]  
GO
ALTER DATABASE [MyDatabase]
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE ([MyDatabase_Log], 1);
GO
ALTER DATABASE [MyDatabase]
SET RECOVERY FULL;
GO

The script above worked fine for me and you will find similar solutions on the internet. If you analyze the script you will see that it changes the recovery model from FULL to SIMPLE which means that the database will not longer log transactions that occur in the database. After the change we execute the DBCC SHRINKFILE command that will try to shrink the file stated by the logical filename (MyDatabase_Log) to the desired size in megabytes (the second parameter to the DBCC command). When the file has been shrunk we restore the recovery model to FULL that was the original setting for the database; of course this all depends on your scenario, but my guess if you read this is that you are using the FULL recovery model. 

While looking around I found another older solution that were used earlier to accomplish sort of the same thing, however it’s recommended not to use the approach mentioned below.

USE [MyDatabase]  
GO
DBCC SHRINKFILE([MyDatabase_Log], 1)
BACKUP LOG [MyDatabase] WITH TRUNCATE_ONLY
DBCC SHRINKFILE([MyDatabase_Log], 1)
GO 

How to expose data for each month in an intervall

I recently stumbled into a problem where I was supposed to display aggregated data based on a time-period for 15 months.

Ok, so this seems to be a trivial thing, right? It is just to write a query such as:

SELECT  CAST(DATEPART(year, Date) AS CHAR(4)) 
          + REPLICATE('0', 2 - LEN(CAST(DATEPART(month, Date) AS CHAR(2))))
          + CAST(DATEPART(month, Date) AS CHAR(2)) MeetingDate,
                COUNT(Date) Occasions
FROM TestData
GROUP BY CAST(DATEPART(year, Date) AS CHAR(4)) 
          + REPLICATE('0', 2 - LEN(CAST(DATEPART(month, Date) AS CHAR(2))))
          + CAST(DATEPART(month, Date) AS CHAR(2))
ORDER BY 1

This query yields in the following result in my scenario:

MeetingDate Occasions 
-------------------------------- ----------- 
200709 1 
200711 1 
200712 1 
200803 1 
200805 1 
200806 3 
200807 1 
200808 3 
200810 2 
200811 1 

(10 row(s) affected)

Well, it is not that simple because there might be gaps in the data, i.e. some months have no records, so the results above displays September, November, December, but not October, which is not our desired result since we want to expose all months between the first and the last occasion even if there is no data for the current month.

So, how do we write a query that gives us this result?

I came across the concept of an auxilliary table of numbers that I read about in Itzik Ben-Gan’s book "SQL Server – Querying". Itzik tells that by using an auxilliary table of numbers it is possible to easily get a numbered sequence that can be used to create a resultset which can be very handy if you don’t have a logical sequence.

After a little brainstorming I came up with the following solution which solved my problem.

First som preparations needed for our example

USE Master 
GO 
CREATE DATABASE Test 
GO 
USE Test 
GO 

Create the table that will contain the numbered sequence, i.e. the auxilliary table of numbers.

This table will only contain numbers from 1 to n.

CREATE TABLE dbo.Nums (Num int) 
GO

Populate the dbo.Nums table with the numbers needed. I only needed a small amount of numbers for my solution, but

if you need more numbers, just add a larger number below or Google for "Auxilliary table of numbers" and "Itzik Ben-Gan".

DECLARE @CurrentNumber int 
SET @CurrentNumber = 0 
WHILE @CurrentNumber < 50 
BEGIN 
SELECT @CurrentNumber = @CurrentNumber + 1 
INSERT INTO dbo.Nums VALUES (@CurrentNumber) 
END 
GO 

Create some test-data for this example

CREATE TABLE TestData ( 
ID int identity not null, 
Date datetime not null 
) 
GO 
INSERT INTO TestData VALUES (dateadd(month, -1, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -2, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -2, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -4, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -4, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -4, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -5, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -6, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -6, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -6, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -7, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -9, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -12, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -13, getdate())) 
INSERT INTO TestData VALUES (dateadd(month, -15, getdate())) 
GO

Get the earliest and latest date from the testdata; these dates will be used to get the number of months for the time-period.

DECLARE @MinDate datetime 
SET @MinDate = (SELECT MIN(Date) FROM testdata) 
DECLARE @MaxDate datetime 
SET @MaxDate = (SELECT MAX(Date) FROM testdata)

Get the number of months between the first and last date.

DECLARE @NumberOfMonths int 
SET @NumberOfMonths = (SELECT DATEDIFF(MONTH, @MinDate, @MaxDate))

Create a table variable to contain the intermediate result.

DECLARE @TempData TABLE ( 
DateTemp varchar(6), 
Occasions int, 
MonthNo int)

Populate the table-variable with the correct number of rows that corresponds to the @NumberOfMonths-variable.

INSERT INTO @TempData 
SELECT CAST(DATEPART(year, DATEADD(MONTH, Num -1, @MinDate)) AS CHAR(4)) 
+ REPLICATE('0', 2 - LEN(CAST(DATEPART(month, DATEADD(MONTH, Num - 1, @MinDate)) AS CHAR(2)))) 
+ CAST(DATEPART(month, DATEADD(MONTH, Num - 1, @MinDate)) AS CHAR(2)), 
0, 
Num 
FROM dbo.Nums 
WHERE Num <= @NumberOfMonths

Update the table-variable with correct data.

UPDATE @TempData 
SET 
Occasions = Occasions + ar.NumberOfOccasions 
FROM ( 
SELECT 
COUNT(Date) NumberOfOccasions, 
DATEDIFF(month, @MinDate, Date) + 1 MonthName 
FROM TestData 
GROUP BY DATEDIFF(month, @MinDate, Date) + 1 
) ar 
WHERE MonthNo = ar.MonthName

Display the correct result from the table variable.

SELECT * FROM @TempData 
ORDER BY MonthNo

Here is the desired result that we wanted

DateTemp Occasions MonthNo 
-------- ----------- ----------- 
200709 1 1 
200710 0 2 
200711 1 3 
200712 1 4 
200801 0 5 
200802 0 6 
200803 1 7 
200804 0 8 
200805 1 9 
200806 3 10 
200807 1 11 
200808 3 12 
200809 0 13 
200810 2 14

(14 row(s) affected)

Clean up and delete the demo database.

USE Master 
GO 
DROP DATABASE Test 
GO