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.

Use a user data type (UDT) in temp table

Every now and then someone wants to use a user-defined data type (UDT) from a database in a temp table.

Initially trying it gives the error:
CREATE TABLE #Test (TestField GrumpyDBA)
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #1: Cannot find data type GrumpyDBA

To enable the datatypes in temp tables, create them in TEMPDB ... and assuming it's not just for a one off script, also in MODEL (so they are rebuilt in tempdb on each startup).

CREATE TYPE GrumpyDBA FROM INT NOT NULL

So far, so obvious, but the issue that now crops up is the following error when a user tries to create the table again:

Msg 15247, Level 16, State 4, Line 2
User does not have permission to perform this action.

It's quite easy to give up at this point, but it's actually an easy update to fix this as well, just run this in TEMPDB (and Model)

GRANT REFERENCES ON TYPE::dbo.GrumpyDBA TO public

And you should now find you can use the UDT in temp tables from the original database.

Error using linked server from 64bit to 32bit SQL

A colleague came across an error using a linked server connection to retrieve data from another server, which gave an error claming:

The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator. Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "". The provider supports the interface, but returns a failure code when it is used.

We knew the table did exist, and the user had permissions.  The same statement also worked from another server, so it wasn't the syntax either.  We assumed it was some issue going from SQL 2008 to SQL 2000, but it seems there is a small issue with using linked servers between 64-bit and 32-bit versions due to the way it queries meta data.

The Microsoft line on fixing this is "To resolve this problem, manually run the Instcat.sql script that is included with SQL Server SP" - which begs the question, why doesn't the SP do this automatically?!

Our fix though was to manually create "sp_tables_info_rowset_64" which the 64-bit server tries to run on the 32-bit box.  There are several blogs that script out the full code for doing this, but this code is almost exactly the same as the proc that already exists on the 32-bit version, namely "sp_tables_info_rowset" - so we found the quickest and tidiest solution to be just to use that, as follows:

USE master GO CREATE PROCEDURE sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = null AS DECLARE @Result INT SET @Result = 0 EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type GO GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

You should now find the error magically goes away.

Fix orphaned SQL user logins on new server

This piece of code helps in the situation where you have attached or restored a database onto a different server or instance from where it originated.

SQL will automatically re-apply permissions for any windows users as the sid is unique across the domain, but for any SQL users you will find that even if the same names exist on the new server, they will not have the required access.

To fix this, you need to use sp_change_users_login with @Action of Auto_fix - this will remap the user sid in the database, to the login sid for this SQL instance.

Kill all connections in a database

Every now and then I've come across situations where you need to drop all connections to a database, so I came up with a little proc to auto generate and execute KILL statements against all processes for a certain database.

My original code for this was titled sp_massmurder_db, and was as follows.

CREATE PROC [dbo].[sp_MassMurder_DB] @DBName varchar(100)  AS    SET NOCOUNT ON    DECLARE @Spid int, @SQL Varchar (1000)    DECLARE DESTROY CURSOR FOR  SELECT spid  FROM master.dbo.sysprocesses (NOLOCK)  WHERE dbid = DB_ID(@DBName)    OPEN DESTROY  FETCH NEXT FROM DESTROY INTO @spid  WHILE @@FETCH_STATUS = 0   BEGIN      SET @SQL = 'KILL ' + CONVERT(Varchar, @SPID)    EXEC (@SQL)    FETCH NEXT FROM DESTROY INTO @spid     END  CLOSE DESTROY  DEALLOCATE DESTROY

Get SQL table column names in a string

SELECT * is all well and good for quick debugging and manually run statements, but when you are inserting the results into a table within a stored proc, the dangers of a SELECT * and the problems caused by changes to the underlying table should be all too obvious.

However, picture the scene - a Friday afternoon and all you want to do is head to the pub, but you know you should finish your code properly.  You have a SELECT * statement in a proc or a view, but it pulls all the fields from a table with over 100 columns ... you're not really going to start typing every field name are you?  Well I'm not - so I would hit ALT-F1 on the table name, cut and paste into excel and add commas - or do a select name + ',' from syscolumns and paste the result into my code.  However, even that is too much effort for a lazy DBA like me, so I bring you <fanfare> sp_tablecolumns.

CREATE PROC sp_tablecolumns @object varchar(100)    AS    SET NOCOUNT ON    DECLARE @STRING VARCHAR(1000)    SET @STRING = ''    SELECT @STRING = @STRING + '[' + name + '], '    FROM syscolumns WHERE id = OBJECT_ID(@object)    ORDER BY ColOrder    SELECT LEFT(@STRING, LEN(@STRING) - 1)

Stick this in the master database for it to run for all db's (with relevant permissions if necessary) or just drop into a single database (if you're not a DBA and your own won't let you near the master db!).  Then execute it or set this up on a hot key, and with one key press you get a nice comma separated string of fields you can replace that * with ... and your beer is still cold!