sentient beings

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
 

A few weeks ago, I noticed that my Lacie Internet Space was offline. Not a real problem, since the LaCie Internet Space will go offline every once in a while. A powercycle is usually enough to get it going again.

But to my dismay, not this time. I powercycled the LaCie Internet Space and it booted allright, the blue light came on and then ... nothing.

I dismantled the complete thing, took out the drive (Hitachi Deskstar 1TB 7200 RPM) and plugged it into my usb/sata converter and again, nothing happened.

Cue slight panic as my entire music collection was stored on this, as well as my wife's photo portfolio. Since the drive did seem to spin up, I suspected the logic board. I scoured the internet for an exact hard disk and found one. In the meantime, I had also bought a second hand LaCie Internet Space, thinking there would be a Hitachi Deskstar inside but it turned out to be a Samsung HD103SI. I discarded the second LaCie Internet Space and put all my hopes on the spare disc, which, by the way, was rather expensive due to the floods in Thailand.

I switched the logic boards, connected the Hitachi to the usb/sata converter and nothing happened. I replaced the logic boards and in a final act of lucidity decided to put the hard drive into the replacement LaCie Internet Space casing. All of a sudden, the disc sprang back to live, started rattling merrily and I could see the disc once again in my network places.

It seemed that the hard disc wasn't at fault but the original LaCie Internet Sace casing. Go figure. Why the drive wasn't recognised by the usb/sata converter is beyond me. I'm copying 1 TB of data to my other NAS right now and I'm never buying anything from LaCie again.

I thought I had tried about everything to get CR2 RAW support up and running in Windows XP. I installed the Canon Codec and tried to open the files with the regular Windows Fax and Picture viewer. FAIL!

I installed Microsoft's Powertoy "Microsoft RAW Image Thumbnailer and Viewer for Windows XP" but that failed with a "cannot load image" message. I was at wit's end. Until ...

Finally, I discovered that Microsoft's Live Photo Gallery that is part of Vista and Windows 7 and that does suppot CR2 RAW files was available for Windows XP. Download it here: http://download.live.com/photogallery and then uncheck all the vile software you don't need. Et voila, CR2 RAW support from your explorer in Windows XP.

Whew. That only took me about 2 hours to fix.