Find that object with sp_find

Personally I don't always trust the SQL depencies report, so I compiled a basic proc that will search the current database for a specific string.

This string could be a proc name, or field name - and this search will return a list of objects in the current database that contain this text string anywhere in their structure (e.g. in a table column, in a view script, in a stored proc).  It also checks the jobs on the server for the string as well - useful if trying to track down where a stored proc is being called from.

Code link: sp_find

Create this in the master database and you can run it in the context of whichever database you are currently connected to.

I find it helpful to map it to a function key in SSMS, then just highlight the text I want to find, and press the relevant key.  Doesn't always work - e.g. if there is a space or a "." in the string - in which case wrap it in single quotes.

If you want to search all databases, you can use this wrapper proc, that cycles through all objects in all databases - so obviously it will take a bit longer.

Code link: sp_find_anydb

Both of them have an @IgnoreCommentedOut parameter, which also attempts to strip out any commented text from proc code (either via the '--' prefix, or contained within /* and */ identifiers).  Again this will slow down the code, so is not applied by default.

Move SQL table to a different schema

Moving a table in SQL from one schema to another is actually very simple, but as it can't be done via Management Studio, remembering the syntax can be a pain.

Logically you think you want to ALTER TABLE to move it to a different schema, but actually you need to ALTER SCHEMA to give it a table.

What you need to do is:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.TableName

Very easy ... although I know I'll forget that again next time I need to do it ... but at least now I know where I can find the right syntax easily!