SELECT GETDATE();

declare @name nvarchar(32), 
    @sql nvarchar(1000)

-- find constraint name
select @name = O.name 
from sysobjects AS O
left join sysobjects AS T
    on O.parent_obj = T.id
where isnull(objectproperty(O.id,'IsMSShipped'),1) = 0
    and O.name not like '%dtproper%'
    and O.name not like 'dt[_]%'
    and T.name = 'MyTable'
    and O.name like 'DF__MyTable__MyColu%'

-- delete if found
if not @name is null
begin
    select @sql = 'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @name + ']'
    execute sp_executesql @sql
end

-- do your ALTER TABLE here

-- replace the constraint
select @sql = 'ALTER TABLE [MyTable] ADD CONSTRAINT [' + @name + '] DEFAULT (0) FOR [MyColumn]'
execute sp_executesql @sql

select 'table_name'=object_name(i.id)  ,i.indid
,'index_name'=i.name  ,i.groupid
,'filegroup'=f.name  ,'file_name'=d.physical_name
,'dataspace'=s.name from sys.sysindexes i
,sys.filegroups f  ,sys.database_files d
,sys.data_spaces s
where objectproperty(i.id,'IsUserTable') = 1
and f.data_space_id = i.groupid
and f.data_space_id = d.data_space_id
and f.data_space_id = s.data_space_id
order by f.name,object_name(i.id),groupid
go

CREATE TABLE XmlImportTest ( xmlFileName VARCHAR(300), xml_data xml ) 
GO 
DECLARE @xmlFileName VARCHAR(300) 
SELECT @xmlFileName = 'c:\TestXml.xml' 
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET 
EXEC(' INSERT INTO XmlImportTest(xmlFileName, xml_data) SELECT ''' + @xmlFileName + ''', xmlData FROM ( SELECT * FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA ) AS FileImport (XMLDATA) ') 
GO 
SELECT * FROM XmlImportTest 
DROP TABLE XmlImportTest 

require 'win32ole'

class SqlServer
    # This class manages database connection and queries
    attr_accessor :connection, :data, :fields
	attr_writer :username, :password

    def initialize(host, username = 'sa', password='')
        @connection = nil
        @data = nil
	@host = host
	@username = username
	@password = password
    end

    def open(database)
        # Open ADO connection to the SQL Server database
        connection_string =  "Provider=SQLOLEDB.1;"
        connection_string << "Persist Security Info=False;"
        connection_string << "User ID=#{@username};"
        connection_string << "password=#{@password};"
        connection_string << "Initial Catalog=#{database};"
        connection_string << "Data Source=#{@host};"
        connection_string << "Network Library=dbmssocn"
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_string)
    end

    def query(sql)
        # Create an instance of an ADO Recordset
        recordset = WIN32OLE.new('ADODB.Recordset')
        # Open the recordset, using an SQL statement and the
        # existing ADO connection
        recordset.Open(sql, @connection)
        # Create and populate an array of field names
        @fields = []
        recordset.Fields.each do |field|
            @fields << field.Name
        end
        begin
            # Move to the first record/row, if any exist
            recordset.MoveFirst
            # Grab all records
            @data = recordset.GetRows
        rescue
            @data = []
        end
        recordset.Close
        # An ADO Recordset's GetRows method returns an array 
        # of columns, so we'll use the transpose method to 
        # convert it to an array of rows
        @data = @data.transpose
    end

    def close
        @connection.Close
    end
end

declare @search varchar(50)
SET @search = 'searchterm'

SELECT    
     ROUTINE_NAME,
     ROUTINE_DEFINITION
FROM    
    INFORMATION_SCHEMA.ROUTINES
WHERE    
    ROUTINE_DEFINITION LIKE @search
ORDER BY
    ROUTINE_NAME

SELECT TOP 1 column FROM table
ORDER BY NEWID()

<%
str_ConnectionString = "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=database;User Id=user;Password=pwd;"
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open str_ConnectionString

set rs = Server.CreateObject("ADODB.recordset")
strSQL="select * from tablename"

rs.Open strSQL, oConn

do until rs.EOF
	for each x in rs.Fields
		response.write x.value & "|"
	next
	rs.MoveNext
	response.write "<br>"
loop	

rs.Close

oConn.Close
set oConn = Nothing
%>

-- Declare variables

declare @count int, @max int, @day varchar(10), @path varchar(1000), @dbname varchar(55)
declare @device varchar(255), @physical varchar(1255), @lastbackup smalldatetime

-- Set count to start at database ID +1 
-- master	1
-- tempdb	2
-- model		3
-- msdb		4
-- pubs		5
-- Northwind	6

set @count = 7

-- Set maximum no of databases on server

--set @max = (select count(*) from master..sysdatabases) +1
set @max = 12

-- Set backup path

set @path='D:\SQL\backups\'

-- Get unique cycle number
-- For weekly cycle - datepart(dw,getdate())
-- For monthly cycle - datepart(dd,getdate())
-- For yearly cycle - datepart(dy,getdate())

set @day=datepart(dw,getdate())

-- While the starting number of databases is smaller than the maximum number
-- of databases:
-- 1.	Check if there is already a backup device made for this
-- day of the cycle. If there isn't create the device.
-- 2.	See if a backup has already been done for this day of the cycle. If 
-- it hasn't do a full backup, otherwise do a differential backup.
-- 3.	Repeat for next database.

while (@count < @max)
begin
	
	set @dbname = (select [name] from master..sysdatabases where dbid=@count)
	set @device=@dbname + '_' + @day
	
	if (select count(*) from master..sysdevices where name = @dbname + '_' + @day)=0 begin
		set @physical=@path+@device + '.bak'
		EXEC sp_addumpdevice 'disk', @device, @physical
	end
	
	begin
	
		set @lastbackup=(select max(backup_finish_date) 
		from msdb.dbo.backupmediafamily, msdb.dbo.backupset
		where msdb.dbo.backupset.media_set_id=msdb.dbo.backupmediafamily.media_set_id
		and logical_device_name=@device
		)
		
		if datepart(dd, @lastbackup)!=datepart(dd,getdate()) begin
			BACKUP DATABASE @dbname TO @device WITH INIT
		end
		else begin
			BACKUP DATABASE @dbname TO @device WITH DIFFERENTIAL
		end 
	
	end  
	
	set @count = @count + 1  

continue 
break

end 

development:
  adapter: sqlserver
  mode: odbc
  dsn: Driver={SQL Server};Server=<your server>;Database=<your db>;Trusted_Connection=yes;