Search Through entire database for a value

By Garyjmurphy | Published: October 7, 2011

Query for searching the entire database for a string value

To run this query in your own senerio, just change the ‘Cal’ to any string you desire



DECLARE @Database Varchar(255)
DECLARE @Schema Varchar(255)
DECLARE @TableName Varchar(255)
DECLARE @ColumnName Varchar(255)
DECLARE @SqlCmd Varchar(255)
DECLARE @Search Varchar(1000)

SET @Search = 'Gary'
SET @Search = '''%'+@Search+'%'''

--PRINT @Search

-- create temp table

CREATE TABLE #Results (TableName Varchar(255), ColumnName Varchar(255), Value Varchar(1000))

-- DROP TABLE #Results

DECLARE cur CURSOR READ_ONLY
FOR

SELECT A.TABLE_CATALOG, A.TABLE_SCHEMA, A.TABLE_NAME, B.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES A
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS B ON A.TABLE_NAME = B.TABLE_NAME
WHERE A.TABLE_TYPE = 'BASE TABLE'

OPEN cur

FETCH NEXT FROM cur
INTO @Database, @Schema, @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN

--PRINT @Database, @Schema, @TableName, @ColumnName

PRINT '@Database: '+@Database
PRINT '@Schema: '+@Schema
PRINT '@TableName: '+@TableName
PRINT '@ColumnName: '+@ColumnName

SET @SqlCmd = 'INSERT INTO #Results (TableName, ColumnName, Value)'
SET @SqlCmd = @SqlCmd + ' SELECT '''+@TableName+''','''+@ColumnName+''',['+@ColumnName+'] FROM '+@Database+'.'+@Schema+'.'+@TableName+' WHERE ['+@ColumnName+'] LIKE '+@Search
PRINT @SqlCmd
exec (@SqlCmd)

FETCH NEXT FROM cur
INTO @Database, @Schema, @TableName, @ColumnName

END

CLOSE cur
DEALLOCATE cur

SELECT * FROM #Results

DROP TABLE #Results
Posted in SQL | Leave a comment

Kill Active Processes for a particular SQL Database

By Garyjmurphy | Published: October 10, 2010

Below is a SQL script that I use all the time when it comes down to hung procedures and any unwanted processes that are left open. This below T-SQL could also be turned into a procedure quite easily and used as a cleanup job before running any backups. 



DECLARE @p_SPID int, 
@p_SQL nvarchar(2000),
@dbName nvarchar(100)

-- Change the 'Database Name' to the database that you intend to kill the processes for
SET @dbName = 'Database Name'

DECLARE #cur_Processes CURSOR FOR 
SELECT p.SPID FROM master.dbo.sysprocesses AS p JOIN master.dbo.sysdatabases AS d ON( d.dbid = p.dbid ) 
WHERE d.Name = @dbName AND p.SPID > 50 
-- AND spid >= 51 (because spids of 50 or less are reserved for internal use.) 

OPEN #cur_Processes 

FETCH NEXT 
FROM #cur_Processes INTO @p_SPID 

WHILE @@FETCH_STATUS = 0 

BEGIN SET @p_SQL = 'KILL ' + CONVERT( nvarchar(30), @p_SPID ) 

PRINT @p_SQL EXECUTE( @p_SQL ) 

FETCH NEXT FROM #cur_Processes INTO @p_SPID END 

CLOSE #cur_Processes 
DEALLOCATE #cur_Processes
Posted in Procedures, SQL | Leave a comment

Sql Object Schema

By Garyjmurphy | Published: June 30, 2010

So inorder to Find a table schema in sql you have two options



-- this will show all tables within this database
SP_TABLES

-- this is a more selective search
select * from
INFORMATION_SCHEMA.tables
WHERE TABLE_NAME =

this can also be done with columns



-- this will show all column definations within the table specfied
SP_Columns

-- this is a more selective search on one or many views at one time
select * from
INFORMATION_SCHEMA.columns
WHERE TABLE_NAME =
Posted in SQL | Leave a comment

using OPENROWSET to import excel files into Sql tables

By Garyjmurphy | Published: June 7, 2010

As I am currently looking into ways in which I could automate dataloading from multiple file formats using stored procedures. I stumbled accross OPENROWSET, which proves to have more than what i bargined for with being able to support multple file types which include (access, Excel, csv, text)

So for this post I am looking into the Excel data loading. I know what you are all thinking, that there are many more better ways out there to load data into a sql table than using OPENROWSET, but I am Limited to using Sql server 2000 and creating a stored procdure like below allows the importer to have more flexibility that if i was to say use a DTP package or other build in import/export functions.

So below is a script for a stored procedure that helps handle the OPENROWSET function. With only 3 parameters to specify, inorder to load an excel file/sheet into a sql table. 


create procedure sp_ImportExcelFile            
            (@Source varchar(1000)
            , @SourceSheet varchar (100)
            , @DestinationTable varchar (100))
as

declare @retval int
EXEC master..xp_fileexist @Source, @retval output -- check if file exists
 
if @retval = 0
            begin
                        print 'file does not exist.'
                        return
            end
 -- sets default sheet name to Sheet1 if not specfied
if @SourceSheet is null or @SourceSheet = ''
            set @SourceSheet = '[Sheet1$]'  
else
            set @SourceSheet = '[' + ltrim(rtrim(@SourceSheet)) + '$]'

if @DestinationTable is null or @DestinationTable = ''
            set @DestinationTable = substring(@SourceSheet, 2, len(@SourceSheet) - 3) + convert(varchar, getdate(), 126)

exec('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;HDR=YES;Database=' + @Source + ''', ' + @SourceSheet + ')')
Posted in Procedures, SQL | Leave a comment