You cannot improve the performance.

As I have said, what you are doing is very intensive and will take time to execute.

The best whole database string search query I have seen...

Do you have backups of MSDB going back 1 month? If so restore it as a user database, then query sysjobhistory via script.

If you do not have...

Abu Dina (11/20/2012)

---

Thanks Tony.

I have more questions sorry! if anyone can point me in the right direction I'd be eternally grateful!

1) The new box will not have any locally installed...

Not being stupid right to raise your concern but them accounts are created for the service regaurdless of the account which runs them. It does this so that you...

Bhuvnesh (11/20/2012)

---

anthony.green (11/20/2012)

---

Bhuvnesh (11/20/2012)

---

tonyarp05 61903 (11/20/2012)

---

We are developing one tool(Backend : SQL server), that tool should be search the string from entire database.

But i have some codes,which takes min 30...

Any it does not matter as it will execute the procedure in every database you have in sys.databases unless you specifically exlcude it in the exclusions list.

Uncomment the SELECT @sql...

Use my code, the only thing you need to do is to replace the the text schema.procedure with the name of your procedure eg. dbo.thisismyprocedure

Then remove the spaces between the...

Bhuvnesh (11/20/2012)

---

tonyarp05 61903 (11/20/2012)

---

We are developing one tool(Backend : SQL server), that tool should be search the string from entire database.

But i have some codes,which takes min 30 to 45mins.

Even...

Another way would be to use the following code

DECLARE @sql NVARCHAR(MAX)

SELECT @sql =

REPLACE(

CAST(

(

SELECT 'USE ' + QUOTENAME(name) +';' + CHAR(13) + CHAR(10) +

'EXECUTE schema.procedure;'+CHAR(13)+CHAR(10)

FROM sys.databases WHERE name NOT IN...

No, as I have said what you are wanting to to is very costly in performance as it uses so many resources to look through each table and each column...

There is no way to enforce a naming convention when your typing code but you can enforce object names where you have to create a table using a prefix for...

Not a problem, happy to help.

Does the same procedure exist in all 20 databases and does it need any special paramateres to run?

TARFU

I am guessing the login once existed and you set it to remember the username and password? If so you need to remove the following file and then restart...