SQL Server – searching all columns and tables

I’ve inherited a legacy app at work, the developer having gone on to pastures new. The handover was a little light in details especially in the area of the database. Of course, the first bit of work that comes in requires a change to a value that’s held in different columns in different tables in the database, and I have to find them.

Therefore with a little bit of work I came up with this:

DECLARE @tempTable TABLE(rowId INT IDENTITY(1,1), tableName sysName, columnName sysName, searchSql VARCHAR(2000), dataFound BIT)
DECLARE @i INT
DECLARE @max INT
DECLARE @DataExists BIT
DECLARE @sql NVARCHAR(2000)
DECLARE @parameter NVARCHAR(2000)
DECLARE @searchString varchar(2000)

set @searchString = 'Text To Find'

INSERT INTO @tempTable(tablename, columnName, searchSql)
SELECT      table_name, 
            column_name, 
            'select @DataExists=1 from [' + table_name + '] where [' + column_name + '] like ''%' +@searchString + '%'''
  FROM information_schema.columns
WHERE data_type = 'varchar'
  ORDER BY table_name, column_name
  
SELECT @i=1, @max = MAX(rowId)
FROM @tempTable  
 
SET @parameter = '@DataExists Bit OUTPUT'

WHILE @i < @max
BEGIN
      SELECT @sql = searchSql 
        FROM @tempTable 
       WHERE rowId = @i
      
       set @dataExists = 0
      
       EXEC sp_executeSql @sql, @parameter, @DataExists = @DataExists OUTPUT
      IF @DataExists = 1
            UPDATE @tempTable SET DataFound = 1 WHERE rowId = @i
            
      SET @i = @i + 1
      
END

SELECT distinct tablename, columnname
  FROM @temptable 
 WHERE datafound =1
Advertisements

Microsoft ALM

I was very impressed overall by the latest product suite and the improvements to our workflow it would enable.

While we as the web development team already use TFS for version control, and are starting to look at it for user stories and task tracking, the possibilities of having it manage all the complete workflow from requirements and specifications, through the development process, then testing, and finally the release process. Keeping the requirements linked to the tasks, the source code, and the tests.

 Let’s hope we can start using this fully in the near future.

First experiences with Azure

I have an MSDN account through work, which gives me some free credits with Azure. We’ve been considering using Azure for hosting and for running test servers and such. Therefore yesterday, I created my account and tried to publish a very simple website to Azure. That’s when I hit some trouble. The firewall in the office is blocking me. I’ve put a request in to have it opened, but who knows what will happen.

In the meantime, I’ve tried it from home, and I’m very impressed. It set up a new website and database in seconds, and published the website. Very smooth. I made a few changes and published again, almost instantly.

The Azure dashboard is excellent, easy to navigate and lots of information. I’m now planning on running something a little more complex with services, etc. to see how it goes. I also want to look at Service Bus.

But, apart from the firewall issue, which is a work issue not an Azure one, I’m very, very impressed.

Oh, and today I got a call from a nice lady at Microsoft, giving me the details of my dedicated point of contact to help with any support issues. which is a nice touch.