Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I've just updated my blog post to correct the error in the script that you were having Jeff, you can see the updated script here: <a href="http://thesitedoctor.co.uk/blog/search-every-table-and-field-in-a-sql-server-database-updated" rel="noreferrer">Search all fields in SQL Server Database</a></p> <p>As requested, here's the script in case you want it but I'd recommend reviewing the blog post as I do update it from time to time</p> <pre><code>DECLARE @SearchStr nvarchar(100) SET @SearchStr = '## YOUR STRING HERE ##'     -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns of all tables for a given search string -- Written by: Narayana Vyas Kondreddi -- Site: http://vyaskn.tripod.com -- Updated and tested by Tim Gaunt -- http://www.thesitedoctor.co.uk -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010 -- Date modified: 03rd March 2011 19:00 GMT CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))   SET NOCOUNT ON   DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET  @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')   WHILE @TableName IS NOT NULL   BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) &gt; @TableName AND    OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)  ), 'IsMSShipped'    ) = 0 )   WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)   BEGIN SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA    = PARSENAME(@TableName, 2) AND    TABLE_NAME    = PARSENAME(@TableName, 1) AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal') AND    QUOTENAME(COLUMN_NAME) &gt; @ColumnName )   IF @ColumnName IS NOT NULL   BEGIN INSERT INTO #Results EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END    END   SELECT ColumnName, ColumnValue FROM #Results   DROP TABLE #Results </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSearch all tables, all columns for a specific value SQL Server
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USTim
    1. USTim
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POSearch all tables, all columns for a specific value SQL Server
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COActually, I tried the solution in your blog post (it has a minor error), but it somehow always returns the same result. See my comment in your blog post.
      singulars
      1. PO
      1. USAbel
    2. COThanks Abel, I've been meaning to get around to fix that, I've put up two flavours, one which can be run on it's own without creating a stored procedure, the other is a re-usable stored procedure that once you've added to your db you can re-run anytime you want. Hope it helps. Tim
      singulars
      1. PO
      1. USTim
    3. COGreat script, Tim. I just added uniqueidentifier support to suit my needs, but it still works as a charm.
      singulars
      1. PO
      1. USanthares
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload