Getting Foreign Keys both ways in SQL Server

I’m posting this one here for myself. Whenever I get lost finding foreign keys in a SQL Server database I’ll come back to this for a neat and tidy list of all foreign key constraints on a table.

This bit of SQL will go both ways for any given table. First it’ll get you a list of columns referencing other tables, then secondly a list of other tables referencing your table. Just supply it with a table name and you’re good to go.

DECLARE @TABLE_NAME VARCHAR(30) = 'Customer'

-- Foreign keys referencing other tables
SELECT	object_name(f.parent_object_id) ParentTable,
		col_name(fc.parent_object_id,fc.parent_column_id) ColumnName,
		object_name(f.referenced_object_id) RefTable,
		f.name ConstraintName
FROM	sys.foreign_keys f
JOIN	sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
WHERE	f.parent_object_id = object_id(@TABLE_NAME)


-- Foreign keys refrencing this as a parent table
SELECT	object_name(f.parent_object_id) ParentTable,
		col_name(fc.parent_object_id,fc.parent_column_id) ColumnName,
		object_name (f.referenced_object_id) RefTable,
		f.name ConstraintName
FROM	sys.foreign_keys AS f
JOIN	sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
JOIN	sys.tables t ON t.object_id = fc.referenced_object_id
WHERE	object_name (f.referenced_object_id) = @TABLE_NAME

I put this together based off of this StackOverflow answer.

Also check out the command sp_help 'TableName' for a bunch of useful information on a table, including foreign keys.

Generating a year prefixed serial number in SQL

An existing SQL function I was using created year-prefixed serial numbers. This year it eventually ran into a problem where it couldn’t go higher than 201502015. Here’s my complete rewrite of that function:

BEGIN
	DECLARE @SerialNumber int

	-- Get current year as string
	DECLARE @Year varchar(4) = YEAR(GETDATE())

	-- Get current max inmate number
	DECLARE @CurrentMax int
	SELECT	@CurrentMax = MAX(SerialNumber)
	FROM	Serials
	WHERE	SerialNumber LIKE @Year +'%'

	-- Remove only the first instance of the year in the max serial number
	DECLARE @NextSeq int
	SET		@NextSeq = Stuff(@CurrentMax, CharIndex(@Year, @CurrentMax), Len(@Year), '')

	-- Increment sequence number
	SET		@NextSeq = ISNULL(@NextSeq, 0) + 1

	-- Convert sequence number to string for concatenation
	DECLARE @NextStr varchar(5) = @NextSeq

	-- Return zero padded serial number as integer with year prefix
	SET		@SerialNumber = @Year + RIGHT('00000' + @NextStr, 5)

	RETURN @SerialNumber
END

The function above generates an incrementing zero padded serial number with the current year prefixed, as such:

201500001
201500002
...
201502016

What you want to avoid is using the Replace() function to trim out the year from the preceding serial number, because, well, it will Replace() all over that value like nobody’s business. Here I’m using

Stuff(@CurrentMax, CharIndex(@Year, @CurrentMax), Len(@Year), '')

to only trim out the first instance of the year.