I found this script that allows you to search across all fields in all tables of a database for a specific string. It’s helpful when you trying to where data might be stored in a strange/large/new database that you are not intimately familiar with. Being a consultant I have found myself digging though a clients database many times trying to figure how the data is stored and this has saved me from looking in every table manually. I did not come up with this code myself, but I didn’t save the link of the blog where I found it. So...

Here is a sql script that will give a count of all records in every table in your database. This comes from my friend David McCollough. CREATE TABLE #sizes ([name] nvarchar(200), -- BOL says (20), but that obviously wrong [rowcount] varchar(25)) DECLARE @tablename VARCHAR (128) DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' order by TABLE_NAME -- Open the cursor OPEN tables -- Loop through all the tables in the database FETCH NEXT FROM tables INTO @tablename WHILE @@FETCH_STATUS...

  Here is another sql script I got from my friend David McCollough that will allow you to search all tables for a specific field name. Just set the @ColumnName variable and let ‘er rip.   DECLARE @ColumnName varchar(1000) SET @ColumnName = 'PlayerId' select o.name as TableName, c.name as [ColumnName] from sysobjects o inner join syscolumns c on c.id = o.id where o.type = 'u' and c.name = @ColumnName ...

Search functionality is a feature that comes up with many applications. I have written more than one search stored procedure (and screen), but I usually ended up with writing dynamic sql (building a SQL statement and using the exec command, see Figure 1 below). The main disadvantage to dynamic sql statements is that they are not compiled and performance always matters. I was reading CoDe magazine one day and noticed a part of an article that mentioned dynamic sql without dynamic sql. I read it and was quite pleased with the approach. So the basic approach is to have all...

Here is some T-SQL I got from a friend of mine (David Mccoullgh). Just set the @SearchPhrase to search across stored proc, functions, triggers to see where that string is in use. Very helpful if you are changing a field name and want to know where it is in use. DECLARE @SearchPhrase varchar(1000) SET @SearchPhrase = 'PlayerBonus' SELECT DISTINCT sysobjects.name AS [Object Name], case when sysobjects.xtype = 'P' then 'Stored Proc' ...