IDERA has released another new free tool, SQL Column Search, designed to help you find potentially sensitive data anywhere in your SQL Server databases.  Potentially sensitive data can be located anywhere and it can be a challenge to identify where.  Even with data security and audit policies in place sensitive data can be located away from watchful eyes and be ripe for exploitation.  With this tool a DBA can search for common sensitive column name definitions or create custom column name search criteria to find potentially sensitive data to investigate, secure, and audit.

Querying System Information

All catalog views are accessed via a SELECT SQL statement FROM a specific catalog within the sys. namespace.

For example, the following statement can be used to view information about all database tables in the system via the sys.tables catalog:

SELECT
  *
FROM
  sys.tables

LIKE Statement and Wildcard Character

Before we get into how to extract all tables with a particular name, we should briefly explore what the LIKE statement does, as well as the wildcard (%) symbol, and how they are used together.

LIKE is used in a query to determine if a particular pattern of characters (typically the values of a specified column) match a formatted string of characters.

LIKE is often also used in conjunction with the % character, which represents a wildcard when attempting to match the pattern. When a % wildcard character is present in the pattern string, it indicates that any characters can be present in that location of the pattern string and still be considered a match.

For example, if we want to find all books where the title begins with “The” but can contain any characters thereafter, we’d use a statement like so:

SELECT
  title,
  primary_author,
  published_date
FROM
  books
WHERE
  title LIKE 'The%'

Observant readers might realize that the above pattern would not only match titles that had “The” at their beginning, but also any titles with words simply starting with the three letters “The” as well. Since % wildcards match any characters, if we only want to check for titles with the word “The”, adding a space is more appropriate:

SELECT
  title,
  primary_author,
  published_date
FROM
  books
WHERE
  title LIKE 'The %'

Selecting Tables Containing a Column Name

With our basic knowledge of both catalog views and the LIKE statement, we are now equipped to lookup all the tables in our system that contain a particular column name:

SELECT
  sys.columns.name AS ColumnName,
  tables.name AS TableName
FROM
  sys.columns
JOIN sys.tables ON
  sys.columns.object_id = tables.object_id
WHERE
  sys.columns.name = 'ColumnName'

We need to combine information from two catalogs, sys.tables and sys.columns, so we’re using a JOIN statement. The two are associated by the object_id field, so we JOIN on that field.

From there, it’s a simple matter of selecting the ColumnName and TableName of our results, and finally, of course, only looking up records where sys.columns.name is equal to our ColumnName string.

However, this query will only find exact matches of the column name. If we want to find partial matches, we can use LIKE and % wildcard characters instead:

SELECT
  sys.columns.name AS ColumnName,
  tables.name AS TableName
FROM
  sys.columns
JOIN sys.tables ON
  sys.columns.object_id = tables.object_id
WHERE
  sys.columns.name LIKE '%ColumnName%'

There we have it! A simple query to look up all tables and associated columns with a particular (or similar) column name in them.

How to quickly search for SQL database data and objects in SSMS

Frequently, developers and DBAs need to search databases for objects or data. If a database function that contains a specific table column or a variable name, or for a table that contains specific data is searched, the simple solution for finding those values, does not exists.

As there is no out-of-the-box solution in SQL Server management Studio, nor Visual Studio, here are a couple of options that can be used:

Searching for data in tables and views

Using SQL to search for specific data in all tables and all columns of a database is far from an optimal solution. There are various SQL scripts with different approaches that can be used to obtain this information, what they have in common is that they all use cursors and system objects:

DECLARE
   @SearchText varchar(200),
   @Table varchar(100),
   @TableID int,
   @ColumnName varchar(100),
   @String varchar(1000);
--modify the variable, specify the text to search for SET @SearchText = 'John';
DECLARE CursorSearch CURSOR
    FOR SELECT name, object_id
        FROM sys.objects
      WHERE type = 'U';
--list of tables in the current database. Type = 'U' = tables(user-defined) OPEN CursorSearch;
FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
WHILE
       @@FETCH_STATUS
       =
       0
    BEGIN
        DECLARE CursorColumns CURSOR
            FOR SELECT name
                  FROM sys.columns
                WHERE
                       object_id
                       =
                       @TableID AND system_type_id IN(167, 175, 231, 239);
        -- the columns that can contain textual data        
--167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar        
OPEN CursorColumns;
        FETCH NEXT FROM CursorColumns INTO @ColumnName;
        WHILE
               @@FETCH_STATUS
               =
               0
            BEGIN
                SET @String = 'IF EXISTS (SELECT * FROM '
                            + @Table
                            + ' WHERE '
                            + @ColumnName
                            + ' LIKE ''%'
                            + @SearchText
                            + '%'') PRINT '''
                            + @Table
                            + ', '
                            + @ColumnName
                            + '''';
                EXECUTE (@String);
                FETCH NEXT FROM CursorColumns INTO @ColumnName;
            END;
        CLOSE CursorColumns;
        DEALLOCATE CursorColumns;
        FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
    END;
CLOSE CursorSearch;
DEALLOCATE CursorSearch;

The drawbacks of this solution are: use of cursors, which are generally inefficient, high complexity, a lot of time needed for execution, even on small databases. Another disadvantage is that it can be used to search for text data only. To search for other data types, such as time and datetime, a new code must be written.

Searching for objects

Searching for a database object name or object definition is a bit easier than searching for specific text. There are several methods that can be used. However, all of these methods include querying system objects.

The following SQL examples search for the specified text – the @StartProductID variable – in stored procedures. When searching for objects in other database object types – functions, triggers, columns, etc., or in multiple database object types at the same time, the SQL shown above should be modified accordingly.

INFORMATION_SCHEMA.ROUTINES

Use SQL that queries the INFORMATION_SCHEMA.ROUTINES view to search for a specific parameter in all procedures. The INFORMATION_SCHEMA.ROUTINES view contains information about all stored procedures and functions in a database. The ROUTINE_DEFINITION column contains the source statements that created the function or stored procedure:

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%@StartproductID%' 
    AND ROUTINE_TYPE='PROCEDURE'

And the result is:

It is not recommended to use INFORMATION_SCHEMA views to search for object schemas stored in the ROUTINE_SCHEMA column. Use the sys.objects catalog view instead:

sys.syscomments view

Query the sys.syscomments view, which contains information about every stored procedure, view, rule, default, trigger, and CHECK and DEFAULT constraints in a database. The query checks for a specific text in the text column, which contains the object DDL:

SELECT OBJECT_NAME( id )
  FROM SYSCOMMENTS
  WHERE text LIKE '%@StartProductID%' AND OBJECTPROPERTY(id , 'IsProcedure') = 1
  GROUP BY OBJECT_NAME( id );

The result is:

This method is not recommended because the sys.syscomments table will be removed in the future versions of SQL Server.

sys.sql_modules view

Query the sys.sql_modules view which contains the name, type and definition of every module in a database:

SELECT OBJECT_NAME( object_id )
  FROM sys.sql_modules
WHERE
       OBJECTPROPERTY(object_id , 'IsProcedure')
       =
       1 AND definition LIKE '%@StartProductID%';

The results are the same as for the previous method:

Other sys schemaviews

Query sys.syscomments, sys.schemas and sys.objects views. The sys.schemas view contains a row for every database schema. The sys.objects view contains a row every user-defined, schema-scoped object in a database. Note that it doesn’t contain the triggers information, so the sys.triggers view need to be used to search for object names or object definitions in triggers:

DECLARE
 @searchString nvarchar( 50 );
SET @searchString = '@StartProductID';
SELECT DISTINCT
    s.name AS Schema_Name , O.name AS Object_Name , C.text AS Object_Definition
FROM
     syscomments C INNER JOIN sys.objects O
                     ON
     C.id
     =
     O.object_id
                   INNER JOIN sys.schemas S
                   ON
     O.schema_id
     =
     S.schema_id
WHERE
    C.text LIKE
     '%'
   + @searchString
   + '%'
 OR O.name LIKE
     '%'
   + @searchString
   + '%'
ORDER BY
       Schema_name , Object_name;

The returned results are:

The main disadvantage of these methods is that for every change in object types searched, the SQL code need to be changed. To be able to do that, the object structure system needs to be well known, so it can be modified. Searching in multiple object types, and adding additional search criteria, such as including/excluding object names and bodies, or defining the escape character, brings even more complexity to SQL, which is prone to mistakes without proper and time-consuming testing.

For an unexperienced developer, for those which prefer a tested and error-free solution to searching SQL objects and data manually and for those which are not familiar with system objects that hold DDL information about database objects, use ApexSQL Search.

ApexSQL Search is a SQL search add-in for SSMS and Visual Studio. It can search for text within database objects (including object names), data stored in tables and views (even encrypted ones) and repeat previous searches in a single click.

To search for data in tables and views:

1. In SQL Server Management Studio or Visual Studio’s menu, click ApexSQL Search
   
2. Click on the Text search command:

1. In the Search text field, enter the data value that needs to be searched
2. From the Database drop-down menu, select the database to search in
3. In the Select objects to search tree, select the tables and views to search in, or leave them all checked
4. Select whether to search in views, numeric, text type, uniqueidentifier and date columns, by selecting the corresponding check boxes, and whether to search for an exact match. If searching in date columns, specify the date format:

1. Click the Find button. The grid will be populated with the database tables and views that contain the entered value:

1. Click the ellipse button in the Column value to see the found object details:

To search for objects:

1. In SQL Server Management Studio or Visual Studio’s menu, from the ApexSQL menu, click ApexSQL Search.
2. Select the Object search command:

1. In the Search text field, enter the text that needs to be searched (e.g. a variable name)
2. From the Database drop-down menu, select the database to search in
3. In the Objects drop-down list, select the object types to search in, or leave them all checked
4. Select whether to search in object, column, index names, object bodies, system objects, by selecting the corresponding check boxes, whether to search for an exact match and which escape character to use
5. Click the Find button:

The grid will be populated with the database objects that contain the specified object.

1. Double-click the object in the Object search grid and it will be highlighted in the Object Explorer:

SQL Server Management Studio and Visual Studio don’t provide search options for a database object name, object definition and data. SQL queries that search for these are complex, slow and require knowledge of SQL Server system objects. Use ApexSQL Search to dig through databases and finds needed data and objects.

SQL Column Search on cloud for AWS

Major Features of SQL Column Search

Find Potentially Sensitive Data
Potentially sensitive data can be located anywhere and it can be a challenge to identify where. Even with data security and audit policies in place sensitive data can be located away from watchful eyes and be ripe for exploitation. With this tool a DBA can search for common sensitive column name definitions or create custom column name search criteria to find potentially sensitive data to investigate, secure, and audit.

Includes 45 Pre-configured Sensitive Data Search Strings
Common sensitive data strings such as birth date, income, and password have been preconfigured for you to select from. The flexible design also enables you to define specific strings and to create and save different search profiles to customize exactly what you want to search for. This enables a variety of uses such as helping locate potentially sensitive data, performing database maintenance tasks, or to just find a specific column name.

Search an Entire Instance or a Specific Table
Define your search to look across all databases within an instance, confine it to a specific database, or even to a specific table within a database to give you the search granularity you need.

Export Results to CSV Format for Easy Analysis and Reporting
Search results are presented in a summary grid that lists each database and table combination in which a search was successful, and a details section to see the column names found in each table. You can export the information from the database and table summary grid to CSV format to easily analyze and report results in your favorite spreadsheet application.

• Installation Instructions For Windows

Installation Instructions For Windows

A) Click the Windows “Start” button and select “All Programs” and then point to SQL Column Search

B) RDP Connection: To connect to the operating system,

1) Connect to virtual machine using following RDP credentials :

• Hostname: PublicDNS  / IP of machine
• Port : 3389

Username: To connect to the operating system, use RDP and the username is Administrator.
Password : Please Click here  to know how to  get password .

C) Other Information:

1.Default installation path: will be on your root folder “C:\Program Files\Idera\Idera SQL Column Search”
2.Default ports:

• Windows Machines:  RDP Port – 3389
• Http: 80
• Https: 443

Configure custom inbound and outbound rules using this link

Installation Step by Step Screenshots