How do find a string in SQL Server database

Question

I currently have a db which has been moved to different server, and have a different name, which causes some problems with me as that I am linked to the old server, and don't know all the entries which links this old server and database.

I keep getting this error:

Error: 100000 Severity: 16 State: 1 Error code:-1073548784 - Error description:Executing the query "declare @sql nvarchar(2000) declare @TableEntity v..." failed with the following error: The OLE DB provider "SQLNCLI11" for linked server "10.7.11.20" does not contain the table ""sax2012r2_prod"."dbo"."coresync"". The table either does not exist or the current user does not have permissions on that table.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I've created a SQL Server alias that maps all 10.7.11.20 request to the new server.

Is it similarly possible to do so with the database, or somehow locate the places where it is looking in sax2012r2_prod?

Some tools have search capability across objects and data. One example is Devart's dbForge Studio For SQL Server where even the free Express version can do this. (docs.devart.com/studio-for-sql-server/working-with-search/…) — Brian
– Brian, Commented Jun 11, 2018 at 19:53
Changing the name of the database is a bad idea anyway. Adding in the move to a different server without identifying the places that need to be changed is worse. Your best start would be to look through syscomments with a query like this: SELECT * FROM sys.syscomments WHERE [text] like '%WhatIamlookingfor%' — Laughing Vergil
– Laughing Vergil, Commented Jun 11, 2018 at 19:56
@laughingvergil what does the query exact do, and what about using synonyms — nano
– nano, Commented Jun 11, 2018 at 19:59
@brian not sure what iam looking for it just looks for something that is incorrect in a query, i Guess the finest part indicate a something from a query? — nano
– nano, Commented Jun 11, 2018 at 20:01
The system table sys.syscomments contains the text of stored procedures, views, functions, and other such SQL Code on the server. This searches through that code for examples of whatever string you are looking for. As for aliases, that's not what this does. It looks for places where the DB name is used in a section of code, then lets you know where you need to go to change the DB references. If the table is aliased, you still only need to change the DB references. — Laughing Vergil
– Laughing Vergil, Commented Jun 11, 2018 at 20:10

EzLo · Accepted Answer · 2018-06-12 07:13:52Z

You can use this cursor to search object definitions (triggers, functions, stored procedures, etc.), on all databases of the currently connected instance.

DECLARE @SearchString VARCHAR(100) = 'sax2012r2_prod'


IF OBJECT_ID('tempdb..#FoundProceses') IS NOT NULL
    DROP TABLE #FoundProceses

CREATE TABLE #FoundProceses (
    DatabaseName VARCHAR(1000),
    SchemaName VARCHAR(1000),
    ObjectType VARCHAR(1000),
    ObjectName VARCHAR(1000),
    CreatedDate DATETIME,
    ModifiedDate DATETIME)

DECLARE @c_DatabaseName VARCHAR(100)
DECLARE databaseCursor CURSOR FOR
    SELECT D.Name FROM master.sys.databases AS D WHERE D.database_id > 4 ORDER BY D.Name

OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @c_DatabaseName

DECLARE @v_DynamicSQLInsert VARCHAR(MAX)

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @v_DynamicSQLInsert = '

        USE ' + @c_DatabaseName + '

        INSERT INTO #FoundProceses (
            DatabaseName,
            ObjectType,
            SchemaName,
            ObjectName,
            CreatedDate,
            ModifiedDate)
        SELECT
            DatabaseName = ''' + @c_DatabaseName + ''',
            ObjectType = O.type_desc,
            SchemaName = SCHEMA_NAME(O.[schema_id]),
            ObjectName = O.name,
            CreatedDate = O.create_date,
            ModifiedDate = O.modify_date
        FROM
            ' + QUOTENAME(@c_DatabaseName) + '.sys.objects AS O
        WHERE
            OBJECT_DEFINITION(O.object_id) LIKE ''%' + @SearchString + '%'' AND 
            O.type IN (''P'', ''IF'', ''FN'', ''TF'', ''PC'', ''TR'', ''V'')'

    EXEC (@v_DynamicSQLInsert)

    FETCH NEXT FROM databaseCursor INTO @c_DatabaseName

END

CLOSE databaseCursor
DEALLOCATE databaseCursor


SELECT
    F.DatabaseName,
    F.SchemaName,
    F.ObjectType,
    F.ObjectName,
    F.CreatedDate,
    F.ModifiedDate
FROM
    #FoundProceses AS F
ORDER BY
    F.DatabaseName,
    F.SchemaName,
    F.ObjectType,
    F.ObjectName

It queries the result of the OBJECT_DEFINITION() function on each object from sys.objects.

Collectives™ on Stack Overflow

How do find a string in SQL Server database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related