Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database on a server that's running Microsoft SQL. I would like to find a tool that logs into that server, allows me to search to a string, and returns the tables and fields that contain that string. I"m not a programmer, nor do I know SQL. I saw someone using free desktop software that does this, but I've come to a Google dead-end. Does anyone know a software product that does this?

I don't think monitoring software is what I'm looking for. I might be wrong, but when I look at those products (RedGate, etc), they seem like overkill.

share|improve this question
4  
Red Gate has a free SQL Search tool but I think that it's only accessible through Sql Server Management Studio, so that would have to be installed and you'd have to be able to login to the server. –  swasheck Mar 22 '12 at 14:29
    
Do you have access to any tool that has a query builder? That would let you write query without knowing SQL. –  FrustratedWithFormsDesigner Mar 22 '12 at 15:04
    
@FrustratedWithFormsDesigner I don't have a query builder, but I'm willing to get one. Any suggestions? –  Dorje Mar 22 '12 at 15:08
    
@swasheck Yeah, I saw that, but I can't install things on the server. If I had access, I'd install that and be golden. –  Dorje Mar 22 '12 at 15:10
1  
@swasheck Oooooooo! I can do THAT! –  Dorje Mar 22 '12 at 19:55

2 Answers 2

up vote 1 down vote accepted

Step 1: Download SSMS LINK

Step 2: Download Red Gate SQL Search LINK

Step 3: Install both

Step 4: Connect to your database server

Step 5: Search!

share|improve this answer
    
is the OP looking for a way to search content of the database or schema/objects of the database? Redgate's tool works for the later. –  DaniSQL Mar 23 '12 at 13:40
    
Good point of clarification. I guess I took for granted that they were looking for schema/object definitions and not a query. –  swasheck Mar 23 '12 at 14:57

If you're okay with using a script through SSMS the script below will search through user tables for a certain string. In a system with a lot of tables and columns it will take a long time. :)

set nocount on

declare
    @TableName varchar (100)
    ,@ColumnName varchar (100)
    ,@TableSchema varchar (100)
    ,@min int
    ,@max int
    ,@sql nvarchar (4000)
    ,@string varchar (2000)
    ,@SummaryLevel int

--  search criteria
--      @string fro string to be searched in all relevant fields in all tables
--      @SummaryLevel 0 for detailed results, 1 for search hit count by table
select
    @string = '<the string you want to search for>'
    ,@SummaryLevel = 1

create table #cols (
    colsId int identity (1,1) primary key
    ,TableName varchar (100)
    ,TableSchema varchar (100)
    ,ColumnName varchar (100)
)

create table #results (
    resultsId int identity (1,1) primary key
    ,TableName varchar (100)
    ,ColumnName varchar (100)
    ,ColumnValue varchar (4000)
    ,SummaryLevel int
)

insert into #cols ( TableName, TableSchema, ColumnName )
select
    T.TABLE_NAME
    ,T.TABLE_SCHEMA
    ,C.COLUMN_NAME
share|improve this answer
1  
I'm not sure if you got truncated or what, but OP may want to know from what you're SELECTing. –  swasheck Mar 23 '12 at 18:46
    
Sorry man, SO won't let me put in the whole script. The field accepts it but I get a timeout on the save. I think the back end engine doesn't agree with all my single ticks. :) If you're interested in a script to do this I can post it up somewhere, but I got the impression you were looking for a client side application to do this. –  Jason Cumberland Mar 26 '12 at 18:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.