hunting for a random string

.. in a large (and completely undocumented) database, I’ve found the ways to get all tables that have a column name like ‘string’ and all rows that contain a string. A bit more optimized than my previous search&replace example. select t.name as tname, SCHEMA_NAME(schema_id) as sname, c.name as cname from sys.tables t join sys.columns c […]

close all connections to current database

Useful if the database refuses to go offline for backup/restore DECLARE @DatabaseName nvarchar(50) DECLARE @SPId int DECLARE @CMD varchar(100) SET @DatabaseName = DB_NAME() DECLARE my_cursor CURSOR FAST_FORWARD FOR SELECT SPId FROM MASTER..SysProcesses WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId OPEN my_cursor FETCH NEXT FROM my_cursor INTO @SPId WHILE @@FETCH_STATUS = 0 BEGIN SELECT @CMD […]

How to delete an user from SQL db

Specifically, a MS SQL 200/2005 db. Enterprise Manager and the equivalent Management Studio fail with no helpful details if said user owns any object in the database. To find out first if there are any schemas owned by the user: use [thedatabase]; select s.* from sys.schemas as s inner join sys.database_principals as dp on dp.principal_id […]

remove duplicates

1. create another identical table (lazy way: right-click table, script table as, create to, new query editor window) 2. rename constraints 3. create unique index removeduplicates on newtab (uniqfield) with IGNORE_DUP_KEY 4. insert * from oldtab into newtab You’ll get a warning “ignoring duplicate field” which is good.

search and replace in all tables

SET NOCOUNT ON DECLARE @stringToFind NVARCHAR(100) DECLARE @stringToReplace NVARCHAR(100) DECLARE @schema sysname DECLARE @table sysname DECLARE @count INT DECLARE @sqlCommand NVARCHAR(max) DECLARE @where NVARCHAR(max) DECLARE @columnName sysname DECLARE @object_id INT SET @stringToFind = ‘Smith’ SET @stringToReplace = ‘Jones’ DECLARE TAB_CURSOR CURSOR FOR SELECT B.NAME AS SCHEMANAME, A.NAME AS TABLENAME, A.OBJECT_ID FROM sys.objects A INNER JOIN […]

Bad Behavior has blocked 630 access attempts in the last 7 days.