≡ Menu

SQL Script to Find Text in all Database Tables and Views

I get new applications to work on fairly regularly, and I have found that almost none of them have databases that are normalized to the third normal form. (Shocking, I know.) One that I recently inherited appears to store email addresses in multiple tables instead of in a single table with the user’s other information. This particular gem was sending out emails even to people who thought they had unsubscribed from its notifications because of the classic update anomalies that can result in storing data in multiple places.

I decided to piece together a script for looking at all text columns in the MS SQL Server database to see where else this user’s email address might be stored. Here’s what I came up with.

-- Set database to search
USE dbname
GO
------------------------------------------------------------------------------------------------------------------------------------
-- Set test value here and set @SearchViews to 1 if views are to be included - 0 if not; no other code below changes
DECLARE @TestValue NVARCHAR(MAX) = 'text to search'
DECLARE @SearchViews BIT = 0
------------------------------------------------------------------------------------------------------------------------------------
DECLARE @SQL NVARCHAR(MAX)
DECLARE @varSQL NVARCHAR(MAX)
DECLARE @WhereClause NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
DECLARE @varTableName NVARCHAR(128)
DECLARE @TableSchema NVARCHAR(128)
DECLARE @ColumnName NVARCHAR(128)
	
DECLARE @SchemaObjects TABLE (
	TABLE_NAME NVARCHAR(128), 
	TABLE_SCHEMA NVARCHAR(128)
)

INSERT INTO @SchemaObjects
SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_TYPE = 'BASE TABLE'

IF @SearchViews = 1
BEGIN
	INSERT INTO @SchemaObjects
	SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS
END

DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_NAME, TABLE_SCHEMA FROM @SchemaObjects

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @WhereClause = ''

	DECLARE ColumnCursor CURSOR FAST_FORWARD FOR
	SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @TableSchema AND ((DATA_TYPE IN ('CHAR','NCHAR','VARCHAR','NVARCHAR')) AND (CHARACTER_MAXIMUM_LENGTH IS NOT NULL) AND (CHARACTER_MAXIMUM_LENGTH >= LEN(@TestValue)) OR DATA_TYPE IN ('TEXT','NTEXT'))
	OPEN ColumnCursor
	FETCH NEXT FROM ColumnCursor INTO @ColumnName
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF LEN(@WhereClause) > 0 SET @WhereClause = @WhereClause + ' OR '
		SET @WhereClause = @WhereClause + '(CONVERT(NVARCHAR,[' + @ColumnName + ']) LIKE ''%' + @TestValue + '%'')'
		FETCH NEXT FROM ColumnCursor INTO @ColumnName
	END
	CLOSE ColumnCursor
	DEALLOCATE ColumnCursor	
	IF LEN(@WhereClause) > 0
	BEGIN		
		SET @varTableName = REPLACE(@TableName,' ','_') 
		SET @varSQL = 'DECLARE @' + @varTableName + CHAR(10) + ' TABLE (' + CHAR(10) 
		SELECT @varSQL = @varSQL + ' ' + sc.name + ' ' + 
		CASE WHEN st.name = 'TIMESTAMP' THEN 'DATETIME' ELSE UPPER(st.name) END +
		CASE WHEN st.name IN ('VARCHAR','NVARCHAR','CHAR','NCHAR') THEN '(' + 
		CASE WHEN sc.[length] > 4000 THEN 'MAX' ELSE CAST(sc.[length] AS NVARCHAR) END + ') ' ELSE ' ' END +
		CASE WHEN sc.isnullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ',' + CHAR(10)
		FROM sysobjects so
		JOIN syscolumns sc ON sc.id = so.id
		JOIN systypes st ON st.xusertype = sc.xusertype
		WHERE so.name = @TableName
		ORDER BY sc.colid
 
		SELECT @varSQL = SUBSTRING(@varSQL,1,LEN(@varSQL) - 2) + CHAR(10) + ');' + CHAR(10)
		
		SET @varSQL = @varSQL + 'INSERT INTO @' + @varTableName + CHAR(10) + 'SELECT * FROM [' + @TableSchema + '].[' + @TableName + ']' + CHAR(10) + 'WHERE ' + @WhereClause + ';' + CHAR(10)
		SET @varSQL = @varSQL + 'IF EXISTS(SELECT * FROM @' + @varTableName + ')' + CHAR(10) + 'SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM @' + @varTableName + ';'
		SET @SQL = 'SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM [' + @TableSchema + '].[' + @TableName + '] WHERE ' + @WhereClause		
		PRINT @SQL
		
		PRINT @varSQL
		EXEC (@varSQL)
	END
	FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
END
CLOSE TableCursor
DEALLOCATE TableCursor

Use Mr. CSV Transformer to Generate MongoDB Insert Statements

Building on the Mr. CSV Transformer project that my team developed for the recent Houston Hackathon, I have added the ability to convert the CSV data into MongoDB.

Mr. CSV Transformer with MongoDB functionality

Mr. CSV Transformer with MongoDB functionality

This past weekend, over 200 people attended the second annual Open Houston Hackathon. The project I worked on with my team proved to be very interesting, not only because of its focus – making government more efficient through the sharing of purchasing data using the Price History application developed originally by the GSA, and certainly not least because of the two men I had the privilege of collaborating with – but also because half of the project involved developing a tool that could have wide application apart from the Price History application.

The tool that we developed was forked from another similar product called “Mr. Data Converter” that would allow a CSV or tab delimited file to easily be converted into many other formats. The product I worked on has even greater functionality – the exclusion, reordering, and renaming of columns to produce one that can be uploaded into another application. The tool my team produced is called “Mr. CSV Transformer”.

open source data transformation tool

Clever, no? While many tools exist that can do this (such as SSIS) this is a Web-based tool that can easily be used by someone who may not have the time to learn or use these other more complicated tools. This is a purely HTML / JavaScript / jQuery based tool that does not require any special type of Web server, as all the work is done in the front end. No need for node.js, Rails, ColdFusion, etc. Just Apache, IIS, or whatever flavor of Web server you prefer will work just fine. Also, I am currently hosting it from a GitHub page, so you don’t even have to install it yourself.

Creating a System Admin Login for SQL Server Using the Command Line

This past week I installed Microsoft SQL Server 2014 Developer Edition on my dev box, and immediately ran into a problem I had never seen before.

I’ve installed various versions of SQL Server countless times, and it is usually a painless procedure. Install the server, run the Management Console, it’s that simple. However, after completing this installation, when I tried to log in to the server using SSMS, I got an error like the one below:

SQL Server login error 18456

“Login failed for user… (Microsoft SQL Server, Error: 18456)”

I’m used to seeing this error if I typed the wrong password when logging in – but that’s only if I’m using mixed mode (Windows and SQL Authentication). In this case, the server was set up with Windows Authentication only, and the user account was my own. I’m still not sure why it didn’t add my user to the SYSADMIN role during setup; perhaps I missed a step and forgot to add it. At any rate, not all hope was lost.

The way to fix this, if you cannot log on with any other account to SQL Server, is to add your network login through a command line interface. For this to work, you need to be an Administrator on Windows for the PC that you’re logged onto.

  1. Stop the MSSQL service.
  2. Open a Command Prompt using Run As Administrator.
  3. Change to the folder that holds the SQL Server EXE file; the default for SQL Server 2014 is “C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn”.
  4. Run the following command: “sqlservr.exe –m”. This will start SQL Server in single-user mode.
  5. While leaving this Command Prompt open, open another one, repeating steps 2 and 3.
  6. In the second Command Prompt window, run “SQLCMD –S Server_Name\Instance_Name”
  7. In this window, run the following lines, pressing Enter after each one:
    >CREATE LOGIN [domainName\loginName] FROM WINDOWS
    >GO
    >SP_ADDSRVROLEMEMBER 'LOGIN_NAME','SYSADMIN'
    >GO
    
  8. Use CTRL+C to end both processes in the Command Prompt windows; you will be prompted to press Y to end the SQL Server process.

  9. Restart the MSSQL service.

That’s it! You should now be able to log in using your network login.

Errors in Pasting Data into Datasheet View in SharePoint 2010

As a part of my project to convert a Lotus Notes system to something else, I am making good use of SharePoint Lists to replicate the functionality of some of the less complex Notes applications.

For some of these databases, I am exporting the documents into Structured Text, then converting the structured text to Excel spreadsheets.

A particularly troublesome error occurred when I was trying to copy a large number of rows from an XLSX file into the Datasheet View of a SharePoint 2010 List. I had originally copied several thousand rows into the List, only to realize that I had accidentally selected the wrong range of columns (which made for some interesting looking data when the data types were dissimilar). To fix this problem, I deleted the rows from the Datasheet View, and then attempted to copy-and-paste them again – this time using the correct range.

At this point, SharePoint gave me an several different errors indicating that an incorrect number of columns had been selected, that some cells were read-only, and that pasted data would go off the sheet.

After verifying multiple times that I had indeed selected the right number of columns and the correct columns in the Excel file, I finally decided to reload the Datasheet View in the browser with F5.

Voila, no more error when trying to paste the data. I do not know if this is a “known issue” or not, but it seems that reloading the Web page after deleting data from the Datasheet View is necessary prior to attempting to load new data.

I did find a good site for tips about copying from Excel into a Datasheet View.