Răzvan Cosma's scratchpad

random scripts of random value

Browsing Posts in SQL

.. 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 […]

Linking back to http://sqlzoo.net – as a “thank you” for their service.

Purpose: allow a specific user to backup and restore database, without giving her full access to the filesystem. As dbo, create the proc below, create the login, grant public access to master, grant dbo access on the needed databases, grant execute on dbo.backupng and dbo.restoreng to sqluser. As user, exec backupng @database=’somename’

use angajare alter database angajare set recovery simple backup log angajare with truncate_only dbcc shrinkfile (angajare_log, 1) dbcc shrinkfile (angajare, 1)

EXEC sp_help_revlogin

Connect to the source -> Tasks -> Generate Scripts -> check “Script all objects” -> Script Database Create = true -> Script for Server Version = 200x -> Script Logins = false -> Script Data = true -> Script anything else needed (constraints, fulltext, etc) -> Finish -> change the path for CREATE DATABASE if […]

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 […]

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

Comments off

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.

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 141 access attempts in the last 7 days.