sentient beings

Many queries you write will be about finding the most recent data for a certain type of event, or the highest value for a certain object. While this is a straightforward query type, it gets complicated and ugly when you have to link two or more tables to find your maximum or minimum value. And it gets even more complicated when you don’t have to find the maximum value but the value that is just below the maximum value.

In the rest of the article, I will be talking about the maximum value for a field, but it’s also true of course for the minimum value.
The query type you will encounter most often contains a subquery where the current value is compared to the maximum value for that particular field. A simple example is presented below.

 
SELECT
  MAX(tb2.Field) AS MaxValueForField
FROM
  table1 tb1
    INNER join
  table2 tb2
    ON  tb2.foreign_key = tb1.primary_key
WHERE
  tb2.Field = (
          SELECT
            MAX(_tb2.field)
          FROM
            table1 _tb1
              INNER join
            table2 _tb2
              ON  _tb2.foreign_key = _tb1.primary_key
          WHERE
            _tb1.primary_key = tb1.primary_key
        )
GROUP BY
  tb1.primary_key
 

The subquery can be avoided with a Common Table Expression that not only speeds up the entire query but also offers a solution for another problem that I will be discussing below.

 
;
WITH tb2Sorted
 
AS
 
(
  SELECT
    Row_Number() OVER (partition BY tb1.primary_key ORDER BY tb2.Field DESC) AS RowId,
    Field
  FROM
    table1 tb1
      INNER join
    table2 tb2
      ON  tb2.foreign_key = tb1.primary_key
)
SELECT
  Field AS MaximaleIngangsDatum
FROM
  tb2Sorted
WHERE
  RowId = 1
 

This query performs better than the first one but also offers another advantage.

You can easily find the second or third largest value by simple changing the RowId. Bear in mind that this will not remove duplicates. If the values for Field are 1,1,2,5,5 then RowId 1 will yield 5 and RowId 2 will also yield 5. You can find the top 5 largest values for a certain field by simply changing the where clause to read RowId < 6.

 
;
WITH tb2Sorted
 
AS
 
(
  SELECT
    Row_Number() OVER (partition BY tb1.primary_key ORDER BY tb2.Field DESC) AS RowId,
    Field,
    RowId
  FROM
    table1 tb1
      INNER join
    table2 tb2
      ON  tb2.foreign_key = tb1.primary_key
)
SELECT
  Field AS MaximaleIngangsDatum
FROM
  tb2Sorted
WHERE
  RowId < 6
 

Have you ever found yourself in need of finding a certain string in an entire database? As a freelance BI consultant, this happens to me rather frequently when analysing where and how data is stored and how to retrieve it.

So I came up with this code. It lets you find a string in any column in any table on your database. It's a pretty length search if you have a lot of data and a lot of columns and tables, but it sure beats searching by hand.

Be warned! This may run a pretty long time on large databases.

 
DECLARE @searchString NVARCHAR(MAX)
DECLARE @sqlString NVARCHAR(MAX)
DECLARE @processedString NVARCHAR(MAX)
 
-- Insert your searchstring below
-- Use \ as escape character
-- examples:
--	to search for 'I'm waiting', use 'I\'m waiting'
--	to search for '25%' use '25\%'
 
SELECT
	@searchString = 'naar achter'
 
DECLARE @resultTable	TABLE
(
	table_name	NVARCHAR(255),
	COL_NAME	NVARCHAR(255),
	value_found	NVARCHAR(MAX),
	OBJECT_ID	INT,
	col_id	INT
)
 
SELECT
	@sqlString = '-- start',
	@processedString = ''
 
WHILE
	@sqlString > ''
BEGIN
 
	SELECT
		@sqlString = '',
		@processedString = ''
 
	SELECT
		@processedString = @processedString +
		CASE
			WHEN DATALENGTH(@sqlString) < 7500 -- assume a sql statement will never go beyond 500 characters
			THEN
		'
			select
				''' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + ''' as table_name,
				''' + col.name + ''',
				NULL as value_found,
				' + CONVERT(NVARCHAR,tbl.OBJECT_ID) + ' as object_id,
				' + CONVERT(NVARCHAR,col.column_id) + ' as col_id
		'
			ELSE
				''
		END,
		@sqlString = @sqlString +
		CASE
			WHEN DATALENGTH(@sqlString) < 7500 -- assume a sql statement will never go beyond 500 characters
			THEN
		'
			select
				top 1
				''' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + ''' as table_name,
				''' + col.name + ''',
				' + col.name + ' as value_found,
				' + CONVERT(NVARCHAR,tbl.OBJECT_ID) + ' as object_id,
				' + CONVERT(NVARCHAR,col.column_id) + ' as col_id
			from
				' + QUOTENAME(SCHEMA_NAME(tbl.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(tbl.OBJECT_ID)) + '
			where
				' + col.name + ' like ''%' + @searchString + '%'' escape ''\''
		'
			else
				''
		end
	from
		sys.columns col
			inner join
		sys.tables tbl
			on	col.object_id = tbl.object_id
			and	col.user_type_id in (231, 167, 175, 239)
			 --	nvarchar, varchar, char, nchar
			 --	no text
			left join
		@resultTable rst
			on	col.column_id = rst.col_id
			and	col.object_id = rst.object_id
	where
		rst.table_name is null
 
	print	@sqlString
	print	@processedString	
 
	insert into
		@resultTable
	exec
		(@sqlString)
 
	insert into
		@resultTable
	exec
		(@processedString)
 
	print	'---8<-----------------------------------------------------------------------------------------------'
 
END
 
SELECT
	table_name,
	COL_NAME,
	value_found
FROM
	@resultTable
WHERE
	value_found IS not null
ORDER BY
	1, 2, 3
 

If you've ever found the need to drop or remove all foreign keys on a database then here's a little script that runs without cursors.

 
DECLARE @SQL NVARCHAR(MAX)	
 
SELECT
	@SQL = '-- Start '
 
WHILE @SQL > ''
BEGIN
	SELECT
		@SQL = ''
 
	SELECT
		@SQL = @SQL +
			CASE
				WHEN DATALENGTH(@SQL) < 7500 THEN
					N'alter table ' + QUOTENAME(schema_name(schema_id))
					+ N'.'
					+ QUOTENAME(OBJECT_NAME(parent_object_id))
					+ N' drop constraint '
					+ name
					+ CHAR(13) + CHAR(10) --+ 'GO' + CHAR(13) + CHAR(10)
				ELSE
					''
			END
	FROM
		sys.foreign_keys
 
	PRINT @SQL
	EXEC SP_EXECUTESQL @SQL
	PRINT '---8<------------------------------------------------------------------------------------------'
END
 
GO
 
---8<-------------------------------------------------------------
-- Uncomment the line below to drop all tables too
 
--exec sp_msforeachtable 'drop table ?'
 
PRINT 'All done'
 

If you've ever wanted to get a listing of all the columns in all of the tables in a certain database, here's how. I use this often for writing technical documentation on systems.

This little code will list all of your tables, columns, datatypes, nullable option, identity option and will also indicate primary and foreign keys and default constraints.

 
SELECT
--	distinct
--	quotename(schema_name(tbl.schema_id)) + N'.' + quotename(tbl.name) as TableName,
--	col.column_id,
	col.name AS ColumnName,
	type_name(col.user_type_id)+
	CASE
		WHEN COLUMNPROPERTY(tbl.OBJECT_ID, col.name, 'scale') IS null THEN
			' (' + CONVERT(VARCHAR,COLUMNPROPERTY(tbl.OBJECT_ID, col.name, 'precision')) + ')' --is null --convert(varchar, col.max_length)
		WHEN col.user_type_id in (60, 106, 108, 122) THEN
			' (' + CONVERT(VARCHAR,col.PRECISION) + ',' + CONVERT(VARCHAR, col.scale) + ')'
		ELSE ''
	END	AS DataType,
	CASE col.is_nullable
		WHEN 0 THEN 'NOT '
		ELSE ''
	END
		+	 'NULL' AS Nullable,
	CASE col.is_identity
		WHEN 0 THEN ''
		ELSE 'IDENTITY (' + CONVERT(VARCHAR, idc.seed_value) + ',' + CONVERT(VARCHAR, idc.increment_value) + ')'
	END	AS [IDENTITY],
	CASE
		WHEN ixc.column_id IS not null THEN
			'PK'
		WHEN fkc.constraint_object_id IS not null THEN
			QUOTENAME(schema_name(tb2.schema_id)) + N'.' + QUOTENAME(tb2.name) + N'.' + fcl.name
		ELSE ''
	END AS Key_Constraint,
	CASE
		WHEN dfc.OBJECT_ID IS not null THEN
			dfc.definition
		ELSE ''
	END AS [DEFAULT CONSTRAINT]
FROM
	sys.tables tbl
		INNER join
	(
		SELECT
			QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
			col.name,
			col.column_id,
			col.OBJECT_ID,
			col.is_identity,
			col.is_nullable,
			col.PRECISION,
			col.scale,
			col.user_type_id,
			1 AS SortKey
		FROM
			sys.columns col
				INNER join
			sys.tables tbl
				ON	tbl.OBJECT_ID = col.OBJECT_ID
 
		UNION 
 
		SELECT
			QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
			QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
			0,
			tbl.OBJECT_ID,
			0,
			0,
			0,
			0,
			0,
			0
		FROM
			sys.tables tbl
 
	)	col
		ON	tbl.OBJECT_ID = col.OBJECT_ID
		LEFT join
	sys.key_constraints pkc
		ON	pkc.parent_object_id = tbl.OBJECT_ID
		and	pkc.type = 'PK'
		LEFT join
	sys.index_columns ixc
		ON	ixc.OBJECT_ID = tbl.OBJECT_ID
		and	ixc.index_id = pkc.unique_index_id
		and	ixc.column_id = col.column_id
		LEFT join
	sys.foreign_key_columns fkc
		ON	fkc.parent_object_id = tbl.OBJECT_ID
		and	fkc.parent_column_id = col.column_id
		LEFT join
	sys.columns fcl
		ON	fkc.referenced_column_id = fcl.column_id
		and	fkc.referenced_object_id = fcl.OBJECT_ID
		LEFT join
	sys.tables tb2
		ON	fcl.OBJECT_ID = tb2.OBJECT_ID
		LEFT join
	sys.default_constraints dfc
		ON	dfc.parent_object_id = tbl.OBJECT_ID
		and	dfc.parent_column_id = col.column_id
		LEFT join
	sys.identity_columns idc
		ON	idc.OBJECT_ID = tbl.OBJECT_ID
		and	idc.column_id = col.column_id
WHERE
	tbl.type = 'U'
ORDER BY
	QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name),
	SortKey,
	ColumnName