SQL Scripts

On this page I will give a list of the scripts that are contained on this blog with a link to the relevant posting.  If you have any requests for the next one to put up please let me know, otherwise I'll do whichever I can be bothered!

sp_find / sp_find_anydb
Find references to an object in a database - e.g. which procs have a certain table name in them - in either the current database, or all databases a user has access to on the server (useful for tracking down which database an object is in)

sp_objectHistory - to come
change tracking for SQL objects, along with a history of all previous versions

sp_bigtables - to come
List the size of all tables over 50MB in the database

sp_blockinfo / sp_blockreport - to come
Show details of blocking and blocked processes on a server - can be logged and historically viewed with report proc

sp_running / sp_running_report - to come
List details of the currently active processes on a server - can be logged and historically viewed with report proc

sp_indexesrefresh - to come
Dynamic code to reindex or defrag indexes over a certain level of fragmentation

sp_databaseinfo - to come
Gives an overview of the server and databases (e.g. version, size, recovery mode)

Run against a restored or attached database to re-map SQL logins to the current server

sp_showlinks - to come
List linked servers so non admin users can tell where they connect to

Quick way to get a comma separated string of all field names in a table - useful with insert or select strings

sp_compareData - to come
Compare data between any two same structure tables, even on separate servers

sp_help_text - to come
updated version of the MS sp_helptext - which doesn't wrap text at 255 characters

dynamic loop of KILL statements to close all connections to a given database

sp_SQLBackup_All - to come
script to backup (full or diff) all (or selected) databases on a server

sp_selecttop10 - to come
little proc to assign to a hotkey to quickly do a SELECT TOP 10 * from a highlighted table

PS - yes you're right, it would be helpful if these were in alphabetical order - maybe one day they will be, and then who'll be laughing??