CODEWITHSUNDEEP
sqlsql-servert-sql
nano
nano

Reputation: 85

How do find a string in SQL Server database

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?

Upvotes: 1

Views: 179

Answers (1)

EzLo
EzLo

Reputation: 14189

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.

Upvotes: 1

Related Questions