Database Experts:Miscellonios SQL Queries for SQL Server DBAs

--Trace Session...

select *from sysprocesses where hostname='BHAVESH' and program_name like '.Net%';

dbcc traceon(76)

dbcc INPUTBUFFER(76)

dbcc OUTPUTBUFFER(76)

--Kill Session...

DECLARE @ID INT
DECLARE @MSG CHAR(8)
While 1 = 1
BEGIN
    Set RowCount 1

 SELECT
  @id = spid
 FROM
  Master..Sysprocesses P,
  Master..Sysdatabases D
 WHERE
  D.Name='MediaSurvey'
 AND
  D.dbid = P.dbid


    IF @@rowcount = 0
    break

    SELECT @msg = 'KILL ' + convert(char(8) ,@ID)
    Print @msg
    EXEC( @msg)
    --break
END

GO


--Encrypt/Decrupt Values/Passwords.

CREATE FUNCTION dbo.fnInitRc4
(
    @Pwd VARCHAR(256)
)
RETURNS @Box TABLE (i TINYINT, v TINYINT)
AS

BEGIN
    DECLARE    @Key TABLE (i TINYINT, v TINYINT)

    DECLARE    @Index SMALLINT,
        @PwdLen TINYINT

    SELECT    @Index = 0,
        @PwdLen = LEN(@Pwd)

    WHILE @Index <= 255
        BEGIN
            INSERT    @Key
                (
                    i,
                    v
                )
            VALUES    (
                    @Index,
                     ASCII(SUBSTRING(@Pwd, @Index % @PwdLen + 1, 1))
                )

            INSERT    @Box
                (
                    i,
                    v
                )
            VALUES    (
                    @Index,
                    @Index
                )

            SELECT    @Index = @Index + 1
        END


    DECLARE    @t TINYINT,
        @b SMALLINT

    SELECT    @Index = 0,
        @b = 0

    WHILE @Index <= 255
        BEGIN
            SELECT        @b = (@b + b.v + k.v) % 256
            FROM        @Box AS b
            INNER JOIN    @Key AS k ON k.i = b.i
            WHERE        b.i = @Index

            SELECT    @t = v
            FROM    @Box
            WHERE    i = @Index

            UPDATE    b1
            SET    b1.v = (SELECT b2.v FROM @Box b2 WHERE b2.i = @b)
            FROM    @Box b1
            WHERE    b1.i = @Index

            UPDATE    @Box
            SET    v = @t
            WHERE    i = @b

            SELECT    @Index = @Index + 1
        END

    RETURN
END

ANd this function does the encrypt/decrypt part

CREATE FUNCTION dbo.fnEncDecRc4
(
    @Pwd VARCHAR(256),
    @Text VARCHAR(8000)
)
RETURNS    VARCHAR(8000)
AS

BEGIN
    DECLARE    @Box TABLE (i TINYINT, v TINYINT)

    INSERT    @Box
        (
            i,
            v
        )
    SELECT    i,
        v
    FROM    dbo.fnInitRc4(@Pwd)

    DECLARE    @Index SMALLINT,
        @i SMALLINT,
        @j SMALLINT,
        @t TINYINT,
        @k SMALLINT,
              @CipherBy TINYINT,
              @Cipher VARCHAR(8000)

    SELECT    @Index = 1,
        @i = 0,
        @j = 0,
        @Cipher = ''

    WHILE @Index <= DATALENGTH(@Text)
        BEGIN
            SELECT    @i = (@i + 1) % 256

            SELECT    @j = (@j + b.v) % 256
            FROM    @Box b
            WHERE    b.i = @i

            SELECT    @t = v
            FROM    @Box
            WHERE    i = @i

            UPDATE    b
            SET    b.v = (SELECT w.v FROM @Box w WHERE w.i = @j)
            FROM    @Box b
            WHERE    b.i = @i

            UPDATE    @Box
            SET    v = @t
            WHERE    i = @j

            SELECT    @k = v
            FROM    @Box
            WHERE    i = @i

            SELECT    @k = (@k + v) % 256
            FROM    @Box
            WHERE    i = @j

            SELECT    @k = v
            FROM    @Box
            WHERE    i = @k

            SELECT    @CipherBy = ASCII(SUBSTRING(@Text, @Index, 1)) ^ @k,
                @Cipher = @Cipher + CHAR(@CipherBy)

            SELECT    @Index = @Index  +1
              END

    RETURN    @Cipher
END

--Create New User for Logins.Assign Roles.

USE [master]
GO
CREATE LOGIN [empower] WITH PASSWORD=N'empower', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [CPDbBG2]
GO
CREATE USER [empower] FOR LOGIN [empower]
GO
USE [CPDbBG2]
GO
EXEC sp_addrolemember N'db_datareader', N'empower'
GO
USE [CPDbBG2]
GO
EXEC sp_addrolemember N'db_datawriter', N'empower'
GO


--How to Use OPENDATASOURCE

insert into TableName select * From OPENDATASOURCE('SQLOLEDB','Data Source=InstanceName;User ID=UserName;Password=Password').DatabaseName.dbo.TableName A

--How to Restore and Recover Database.

--With Norecovery
RESTORE DATABASE [DatabaseName] FROM  DISK = N'C:\dmp\DatabaseName.BAK' WITH  FILE = 1,
NORECOVERY,  NOUNLOAD,  STATS = 10
GO




--With Recovery
RESTORE database DatabaseName WITH RECOVERY

--Connect SQL Server from SQLCMD.

SQLCMD -S InstanceName -U UserName -P Password.

--Create Daatabase.

CREATE DATABASE [DatabaseName]  ON  PRIMARY
( NAME = N'DatabaseName', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\DatabaseName.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'DatabaseName_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\DatabaseName_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [DatabaseName]  SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [DatabaseName]  SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [DatabaseName]  SET ANSI_NULLS OFF
GO
ALTER DATABASE [DatabaseName]  SET ANSI_PADDING OFF
GO
ALTER DATABASE [DatabaseName]  SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [DatabaseName]  SET ARITHABORT OFF
GO
ALTER DATABASE [DatabaseName]  SET AUTO_CLOSE OFF
GO
ALTER DATABASE [DatabaseName]  SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [DatabaseName]  SET AUTO_SHRINK OFF
GO
ALTER DATABASE [DatabaseName]  SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [DatabaseName]  SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [DatabaseName]  SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [DatabaseName]  SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [DatabaseName]  SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [DatabaseName]  SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [DatabaseName]  SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [DatabaseName]  SET  DISABLE_BROKER
GO
ALTER DATABASE [DatabaseName]  SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [DatabaseName]  SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [DatabaseName]  SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [DatabaseName]  SET  READ_WRITE
GO
ALTER DATABASE [DatabaseName]  SET RECOVERY SIMPLE
GO
ALTER DATABASE [DatabaseName]  SET  MULTI_USER
GO
ALTER DATABASE [DatabaseName]  SET PAGE_VERIFY CHECKSUM 
GO
USE [DatabaseName]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [DatabaseName]  MODIFY FILEGROUP [PRIMARY] DEFAULT
GO


--Create SQL Server Login.

USE [master]
GO
CREATE LOGIN [UserName] WITH PASSWORD=N'UserName',
 DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english],
 CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

--Change Database Ownership.

EXEC [DatabaseName].dbo.sp_changedbowner @loginame = N'UserName', @map = false
GO

--Get Missing Autoincremented values that deleted or removed .

select top 1 min(id)+1 from for_test
Group by id
Having min(id)+1 not in (select id from for_test)

--Drop Defaults.


A. Dropping a default

If a default has not been bound to a column or to an alias data type, it can just be dropped using DROP DEFAULT. The following example removes the user-created default named datedflt.
Copy

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.objects
         WHERE name = 'datedflt'
            AND type = 'D')
   DROP DEFAULT datedflt
GO

B. Dropping a default that has been bound to a column

The following example unbinds the default associated with the EmergencyContactPhone column of the Contact table and then drops the default named phonedflt.
Copy

USE AdventureWorks2012;
GO
IF EXISTS (SELECT name FROM sys.objects
         WHERE name = 'phonedflt'
            AND type = 'D')
   BEGIN
      EXEC sp_unbindefault 'Person.Contact.Phone'
      DROP DEFAULT phonedflt
   END
GO


--Dettach All SQL Server Database

--For Detach

USE [master]

GO

DECLARE @name VARCHAR(50) -- database name

DECLARE db_cursor CURSOR FOR

SELECT name

FROM master.dbo.sysdatabases

WHERE name  not IN ('master','model','msdb','tempdb')

begin

--Backup master_files information to backup table.

 select * into master_files_bak from  (select distinct DB_NAME(a.database_id) dbname,case when a.physical_name like '%.mdf' then a.physical_name end Datafile,
 case when b.physical_name like '%.ldf' then b.physical_name end logicalfile
 from
 master.sys.master_files a
 inner join
 master.sys.master_files b
 on DB_NAME(a.database_id)=DB_NAME(b.database_id)
 where a.name not in ('master','msdb','model','tempdb')
 ) c
 where Datafile is not null and logicalfile is not null
 and dbname not in ('master','model','msdb','tempdb')
  order by dbname

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN
       EXEC('sp_detach_db '+@name)  
       FETCH NEXT FROM db_cursor INTO @name 
END

CLOSE db_cursor

DEALLOCATE db_cursor

end


--Attach All SQL Server Database.

--For Attach

USE [master]

Go

DECLARE @name        VARCHAR(50)  -- database name
DECLARE @physicalfile    VARCHAR(500) -- database filename with location
DECLARE @logicalfile    VARCHAR(500) -- database filename with location
DECLARE @datafile    VARCHAR(500) -- mdf file without location
DECLARE @logfile    VARCHAR(500) -- ldf file without location
DECLARE @Command    VARCHAR(500) -- SQL Command

DECLARE db_cursor CURSOR FOR
SELECT dbname,Datafile,logicalfile
FROM master.dbo.master_files_bak

begin

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name,@physicalfile,@logicalfile

WHILE @@FETCH_STATUS = 0 
BEGIN 
     
select  @datafile=right((@physicalfile),charindex('\',reverse(@physicalfile), 1) - 1)

select @logfile=right((@logicalfile),charindex('\',reverse(@logicalfile), 1) - 1)
 
SET @Command= 'sp_attach_db '''+ @name+''','+'''D:\MSSQL\DATA\'+@datafile+''','+'''D:\MSSQL\DATA\'+@logfile+''''


print(@Command)


exec(@Command)
      
       FETCH NEXT FROM db_cursor INTO @name,@physicalfile,@logicalfile
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

DROP TABLE [dbo].[master_files_bak]

end




--Map User to Login.

EXEC sp_change_users_login 'Update_One',  'UserName','LoginName';
GO

--Disable/Enable All triggers.


DISABLE Trigger ALL ON ALL SERVER;

ENABLE TRIGGER [Trg_LogonEntry] ON ALL SERVER;


--Grant Priviledges to User of Database.

use [DatabaseName]
GO
GRANT DELETE ON [dbo].[TableName] TO [UserName]

USE [master]
GO
GRANT CONNECT SQL TO [MSAdmin]
GO
ALTER LOGIN [MSAdmin] ENABLE
GO


--Create Job

USE [msdb]
GO
DECLARE @jobId BINARY(16)
EXEC  msdb.dbo.sp_add_job @job_name=N'Disk Usage Mail',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @delete_level=0,
        @description=N'Daily Execute Job For Mailing Free Space Details in SQL Server.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'bhavesh', @job_id = @jobId OUTPUT
select @jobId
GO
EXEC msdb.dbo.sp_add_jobserver @job_name=N'Disk Usage Mail', @server_name = N'SERVER1\DCDB2'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_jobstep @job_name=N'Disk Usage Mail', @step_name=N'Execute_DiskUsageMail',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_fail_action=2,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'EXEC DiskUsageMail',
        @database_name=N'master',
        @database_user_name=N'bhavesh',
        @flags=16
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_name=N'Disk Usage Mail',
        @enabled=1,
        @start_step_id=1,
        @notify_level_eventlog=0,
        @notify_level_email=2,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @delete_level=0,
        @description=N'Daily Execute Job For Mailing Free Space Details in SQL Server.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'bhavesh',
        @notify_email_operator_name=N'',
        @notify_netsend_operator_name=N'',
        @notify_page_operator_name=N''
GO
USE [msdb]
GO
DECLARE @schedule_id int
EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Disk Usage Mail', @name=N'Schedule_DiskUsageMail',
        @enabled=1,
        @freq_type=8,
        @freq_interval=63,
        @freq_subday_type=1,
        @freq_subday_interval=0,
        @freq_relative_interval=0,
        @freq_recurrence_factor=1,
        @active_start_date=20120725,
        @active_end_date=99991231,
        @active_start_time=200000,
        @active_end_time=235959, @schedule_id = @schedule_id OUTPUT
select @schedule_id
GO

--Get the Database Owner Name.

select name,suser_sname(owner_sid) [Owner] from sys.databases

--Get SPID details

SELECT
    SPID                = er.session_id
    ,Status             = ses.status
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,BlkBy              = er.blocking_session_id
    ,DBName             = DB_Name(er.database_id)
    ,CommandType        = er.command
    ,SQLStatement       = st.text
    ,ObjectName         = OBJECT_NAME(st.objectid)
    ,ElapsedMS          = er.total_elapsed_time
    ,CPUTime            = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes
    ,LastWaitType       = er.last_wait_type
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id


--Get Tables Row count and size.

SELECT
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
    sys.tables t
INNER JOIN     
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.NAME NOT LIKE 'sys%' --Change as per requirement.
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, p.Rows
ORDER BY
    t.Name
   
  
   --Offline Database

EXEC sp_dboption N'mydb', N'offline', N'true'
OR
ALTER DATABASE [mydb] SET OFFLINE WITH
ROLLBACK AFTER 30 SECONDS
OR
ALTER DATABASE [mydb] SET OFFLINE WITH
ROLLBACK IMMEDIATE

--Get Database Size Info.

SELECT  db_name(mf.database_id) Database_Name,
        SUM(cast(mf.size as float)) * 8 / 1024/1024 Size_GBs
FROM    sys.master_files mf
        INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE   d.database_id > 4 -- Skip system databases
and mf.type_desc='ROWS'
GROUP BY  db_name(mf.database_id)
Order by 2

--BCP-Bulk Copy Data.

C:\Documents and Settings\admin>bcp "Select top 100 *from sys.sysprocesses" quer
yout c:\department.txt -c -T -SBHAVU\R2

--Get All Views Columns Details.

Select
        sysCols.object_id As ObjectId,
        sysView.name As ViewName,
        sysCols.column_id As ViewColumnId,
        sysCols.name As ViewColumnName,
        sysCols.system_type_id As ViewColumnSystemTypeId,
        sysCols.user_type_id As ViewColumnUserTypeId,
        sysCols.max_length As ViewColumnMaxLength,
        sysCols.precision As ViewColumnPrecision,
        sysCols.scale As ViewColumnScale,
        sysCols.is_nullable As ViewColumnIsNullable,
        sysCols.is_identity As ViewColumnIsIdentity
From
        sys.columns As sysCols inner join sys.views As sysView On sysCols.object_id = sysView.object_id
        where sysView.name='TMeters'

   --Get Database User and Logins Mapping Details.

drop proc usp_logindatabasemapping
go
create proc usp_logindatabasemapping
as
begin
create table #systemdbs (name sysname)
insert #systemdbs
select 'master'
union select 'msdb'
union select 'model'
union select 'tempdb'

create table #dbusers (DatabaseName sysname, UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(max))

create table #dbusersbuffer ( UserName sysname, GroupName sysname, LoginName sysname null,
DefDBName sysname null, DefSchemaName sysname null, UserID int, SID varbinary(max))



declare @Command nvarchar(1012)
set @Command= 'if not exists (select * from #systemdbs where name = ''?'') begin '+char(13)+
+'use ? ; insert #dbusersbuffer exec sp_helpuser'+char(13)
+'insert #dbusers select ''?'', * from #dbusersbuffer'+char(13)
+'truncate table #dbusersbuffer'+char(13)
+'end'
print @Command
exec sp_MSforeachdb @command1 = @Command
select * from #dbusers order by LoginName, UserName
drop table #dbusers
drop table #dbusersbuffer
drop table #systemdbs
end

--Grant Execute Stored procedure role to user.

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor
EXEC sp_addrolemember 'db_executor', 'MXCgUser'


--Get Contraints details.

--Add Check Constraint
ALTER TABLE TableName WITH CHECK ADD  CONSTRAINT [Constraint_Name]
FOREIGN KEY (Reference) REFERENCES RefTable_Name(Column_Name)

--Referecing Table/Column and Referenced Table/Column Queries
select Referencing_Object_name, referencing_column_Name, Referenced_Object_name, Referenced_Column_Name from
(select Referenced_Column_Name = c.name, Referenced_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.rkeyid = o.id) and c.id = o.id and c.colid = f.rkey) r,
(select referencing_column_Name = c.name, Referencing_Object_name = o.name, f.constid from sysforeignkeys f, sysobjects o, syscolumns c
where (f.fkeyid = o.id) and c.id = o.id and c.colid = f.fkey) f
where r.Referenced_Column_Name = f.referencing_column_Name
and r.constid = f.constid
order by f.Referencing_Object_name

--Get the Name of All Contraints
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT%'
--To enable/desable constraints

-- SQL disable all triggers - disable all triggers sql server - t sql disable trigger

EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"

GO

-- SQL disable all constraints - disable all constraints sql server

EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"

GO

--For Single Table Disable Contstraint
alter table TableName CHeck Constraint Constraint_name

--Recycle Error Log.

USE master;
EXEC sp_cycle_errorlog ; 
GO

--Recompile/Validate all stored procedure.

-- table variable to store procedure names
DECLARE @v TABLE (RecID INT IDENTITY(1,1), spname sysname)

-- retrieve the list of stored procedures
INSERT INTO @v(spname)
    SELECT
        '[' + s.[name] + '].[' + sp.name + ']'
    FROM sys.procedures sp
    INNER JOIN sys.schemas s ON s.schema_id = sp.schema_id
    WHERE is_ms_shipped = 0

-- counter variables
DECLARE @cnt INT, @Tot INT
SELECT @cnt = 1
SELECT @Tot = COUNT(*) FROM @v

DECLARE @spname sysname

-- start the loop
WHILE @Cnt <= @Tot BEGIN
    SELECT @spname = spname
        FROM @v
        WHERE RecID = @Cnt

    PRINT 'refreshing...' + @spname

    BEGIN TRY
        -- refresh the stored procedure
        EXEC sp_refreshsqlmodule @spname
    END TRY
    BEGIN CATCH
        PRINT 'Validation failed for : ' +
            @spname + ', Error:' +
            ERROR_MESSAGE()
    END CATCH
    SET @Cnt = @cnt + 1
END


--Add Windows Login.

create login [dca\b.thakkar] from windows;
exec sp_addsrvrolemember 'dca\b.thakkar', 'sysadmin';

--Grant EndPoint to domain user.

GRANT CONNECT ON ENDPOINT::endpoint_mirroring TO [dca\b.thakkar]

--Create Snapshot Database from Standby database.

CREATE DATABASE AdventureWorks_dbss1800 ON
( NAME = AdventureWorks_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks;
GO

--Map Logical Drive to SQL Server.

EXEC xp_cmdshell 'net use Z: "\\0.0.0.0\Database Backup" Password/user:UserName'

NET USE X: /DELETE /YES

--Create Partiton Scheme and function.

CREATE PARTITION FUNCTION pf_tmf_meterfiles_FileExt (int)
 AS RANGE RIGHT
 FOR VALUES (1,12,15,19)

-- Drop Partition Function pf_tmf_meterfiles_FileExt

 
 CREATE PARTITION SCHEME ps_tmf_meterfiles_FileExt
 AS PARTITION pf_tmf_meterfiles_FileExt
 TO ([primary],[fg01], [fg02], [fg03], [fg04])

-- Drop PARTITION SCHEME ps_tmf_meterfiles_FileExt


--Bulk Upload in SQL Server.

BULK
INSERT DF_ChannelNetworkMaster
FROM 'D:\Dump\ChannelNetwork.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

--Create SQL Server Certificate.

CREATE CERTIFICATE DCACertificate
ENCRYPTION BY PASSWORD = 'DCACertificate!@#123abc'
WITH SUBJECT = 'SQL Server Encryption',
EXPIRY_DATE = '12/12/2013';

Go

BACKUP CERTIFICATE DCACertificate TO FILE = 'DCACertificate.Cer'

Go


--Last Time Updated Object Details.


declare @objectid int
select @objectid = object_id from sys.objects where name = 'Tmf_meterfiles'

select top 1 * from sys.dm_db_index_usage_stats where object_id = @objectid
and last_user_update is not null
order by last_user_update