ict.ken.be

--create in master
CREATE PROCEDURE [dbo].[backup_db]
@db            varchar(50),
@backup_path   varchar(254) = 'C:\Databases.Backups\'
AS
DECLARE @backup_device nvarchar(1024);
DECLARE @backup_name nvarchar(127);
DECLARE @device_type nvarchar(50);
SET @backup_device = @backup_path + @db + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak';
SET @backup_name = @db +'-Full Database Backup';
SET @device_type = 'disk';
 
EXEC master.dbo.sp_addumpdevice
@devtype = @device_type,
@logicalname = @db,
@physicalname = @backup_device;
 
BACKUP DATABASE @db TO  @db WITH NOFORMAT, INIT,
NAME = @backup_name , SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
 
EXEC sp_dropdevice @db;
GO

--create in master
CREATE PROCEDURE [dbo].[backup_db_all]
AS
	exec backup_db 'Ken_Databasename1'
	exec backup_db 'Ken_Databasename1'
GO
 
--exec backup_db_all

Call stored procedure from batch file:

• sqlcmd -E -S .\SQLEXPRESS -d master -Q "backup_db 'databaseName','C:\TEMP\'"
• sqlcmd -E -S .\SQLEXPRESS -d master -Q "backup_db_all"

1. Stop the sql server within services
2. Delete the physical files (.mdf , .ldf)
3. Start the sql server
4. Right click the offline database and click delete

USE MASTER 

Declare @dbid int,
        @spid int,
        @str nvarchar(128)
select @dbid = dbid from master..sysdatabases
  where name = 'DB_MESSAGENT'
declare spidcurs cursor for
   select spid from master..sysprocesses where dbid = @dbid
open spidcurs
fetch next from spidcurs into @spid
While @@fetch_status = 0
  Begin
    Select @str = 'Kill '+ convert(nvarchar(30),@spid)
    --exec(@str)
    select @str
    fetch next from spidcurs into @spid
End
Deallocate spidcurs

• In MS SQL Management Studio click "Tools - Options - Designer" and UNcheck the "Prevent saving changes that require table re-creation" box.
• Right-click the table and select "Design" for SQL 2008 or "Modify" for SQL 2005.
• Press F4 to open the "properties" window.
• Make sure that you have the table (!) selected on top of the "properties" window.
• Change the "text filegroup" for the table.
• DO NOT save your changes. The SQL-server most likely will throw a timeout error for your large table.
• Instead choose "Generate change script" and copy the resulting text to the new query window.
• Run the query.
• Be patient. 

Thanks to http://blog.jitbit.com

select o.name, *
from sys.columns c
inner join sys.objects o on o.object_id = c.object_id
where c.Name = N'COLUMN_NAME_TO_FIND'

--get all with table row count

CREATE TABLE #foo(name varchar(128),rows int)

DECLARE @objectName nvarchar(128)

DECLARE cursorTables CURSOR FOR

select o.name 

from sys.columns c

inner join sys.objects o on o.object_id = c.object_id

where c.Name = N'u_username'

OPEN cursorTables

FETCH NEXT FROM cursorTables INTO @objectName

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #foo

EXEC ('SELECT ''' + @objectName + ''' as NAME,COUNT(*) FROM ' + @objectName)

FETCH NEXT FROM cursorTables INTO @objectName

END 

CLOSE cursorTables

DEALLOCATE cursorTables

SELECT * FROM #foo ORDER BY rows DESC

DROP TABLE #foo