Robocopy - powerful tool

Robocopy - powerful tool included with the Microsoft Windows Server® 2003 Resource Kit Tools.

Microsoft has provided the wonderful functionality of 'Copy-paste'(moving a document/contents from one directory to another), but as you know this is all manual work. ITians are always seek for any automated tool that helps them to reduce the work cost & improve their productivity in day to day activity. For example DBA always look for the automated tool. DBA's we always face the problem of moving / copy-pasting backup files / reports from one location to another.

Here we go for the Solutions, Microsoft has introduced the new tool which allows us to either copy-paste or move the files from one location to another, called ROBOCOPY. This tool was introduced earlier but many peoples are aware of this tool. This tool provides automation in copy-paste/ move the files or contents of folder from one location to another. The main advantage of this tool is it copyies only delta between source path & destination path.

For more information you can navigate through....

http://technet.microsoft.com/en-us/magazine/cc160891.aspx

System View maps

Junkies, as we all know while working with SQL we always deal with system tables or views. I would like to share System MAPs which will help you all to know the System structure for SQL.

SQL Server 2005 System Views Map
http://www.microsoft.com/downloads/details.aspx?FamilyID=2ec9e842-40be-4321-9b56-92fd3860fb32&displaylang=en

SQL Server 2008 System Views Map
http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en

Query to retrun all Foreign Key constraints & relationships.

select object_name(fkc.constraint_object_id) FKeyName,
object_name(fkc.parent_object_id) Child_Entity, c2.name Child_Attribute,
object_name(fkc.referenced_object_id) Parent_Entity, c3.name Parent_Attribute
from sys.foreign_key_columns fkc join
sys.columns c2 on fkc.parent_object_id = c2.object_id
and fkc.parent_column_id = c2.column_id join
sys.columns c3 on fkc.referenced_object_id = c3.object_id
and fkc.referenced_column_id = c3.column_id
order by Child_Entity

Difference between using "BETWEEN" & ">= <" clause in Date Search

set nocount on

declare @tblDates table(date datetime primary key clustered)

declare @startdate datetime, @i int, @enddate datetime

set @startdate = '09-Mar-2009'

set @i = 1

while @startdate <= '11-Mar-2009'

begin

set @startdate = dateadd(mi, @i, @startdate)

insert @tblDates values (@startdate)

end

----gives you the day duration updto Minutes

--set @startdate=convert(varchar(10), current_timestamp, 120)---ODBC canonical

--set @enddate = current_timestamp

--set nocount off

--select * from @tblDates where date between @startdate and @enddate

--select * from @tblDates where date >= @startdate and date < @enddate

--set nocount on

---------

--gives you the day duration between one day

set @startdate=convert(varchar(10), current_timestamp, 112)---ISO standard

set @enddate =convert(varchar(10), current_timestamp, 112)

set @enddate = dateadd(day, 1, @enddate)

set nocount off

select * from @tblDates where date between @startdate and @enddate

select * from @tblDates where date >= @startdate and date < @enddate

--first result --- upto 2009-03-10 00:00:00.000

--second result -- upto 2009-03-09 23:59:00.000

set nocount on

-------

Get Last weekday from mention Week day

SET @WkDay = 'Monday'
SET @Today = CURRENT_TIMESTAMP
IF @WkDay != DATENAME(dw, @Today)
BEGIN
WHILE 1 = 1
BEGIN
IF DATENAME(dw, @Today) != @WkDay
BEGIN
SET @Today = DATEADD(DAY, -1, @Today)
--PRINT @Today
--PRINT DATENAME(dw, @Today)
END
ELSE
BEGIN
--PRINT 'DateFound'
--SELECT @Today
BREAK
END
END
END
SET @WkStartDate = @Today
SET @WkEndDate = DATEADD(DAY, 7, @Today)
SELECT @WkStartDate, @WkEndDate

XML Index information

IF OBJECT_ID('T') IS NOT NULL

DROP TABLE T

CREATE TABLE T ( Col1 INT PRIMARY KEY, XmlCol XML )

GO

-- Create primary index

CREATE PRIMARY XML INDEX PIdx_T_XmlCol ON T(XmlCol)

GO

-- Create secondary indexes (PATH, VALUE, PROPERTY)

CREATE XML INDEX PIdx_T_XmlCol_PATH ON T(XmlCol)

USING XML INDEX PIdx_T_XmlCol FOR PATH

GO

CREATE XML INDEX PIdx_T_XmlCol_VALUE ON T(XmlCol)

USING XML INDEX PIdx_T_XmlCol FOR VALUE

GO

CREATE XML INDEX PIdx_T_XmlCol_PROPERTY ON T(XmlCol)

USING XML INDEX PIdx_T_XmlCol FOR PROPERTY

GO

SELECT * FROM sys.xml_indexes WHERE object_id = object_id('T')

INSERT INTO T VALUES (1,

'

Background

Sort

Search

')

GO

SELECT * FROM sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, NULL, 'DETAILED')

GO

DECLARE @index_id int

SELECT @index_id = i.index_id

FROM sys.xml_indexes i

WHERE i.name = 'PIdx_T_XmlCol' and object_name(i.object_id) = 'T'

SELECT * FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')

SELECT * FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')

GO

SELECT i.name, object_name(i.object_id), stats.*

FROM sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, DEFAULT, 'DETAILED') stats

JOIN sys.xml_indexes i ON (stats.object_id = i.object_id and stats.index_id = i.index_id)

WHERE secondary_type is not null

GO

DROP INDEX PIdx_T_XmlCol_PATH ON T

DROP INDEX PIdx_T_XmlCol_VALUE ON T

DROP INDEX PIdx_T_XmlCol_PROPERTY ON T

-- drop primary index

DROP INDEX PIdx_T_XmlCol ON T

-- drop table T

DROP TABLE T

IF OBJECT_ID('T') IS NOT NULL

DROP TABLE T

Script to get Missing Index Information

DECLARE @IndexinfoFor VARCHAR(100)

SET @IndexinfoFor = 'dbo.AnyTableName'

SELECT Index_id, user_seeks, user_scans, user_lookups, user_updates, last_user_seek,

last_user_scan, last_user_lookup, last_user_update, system_seeks, system_scans,

system_lookups, system_updates, last_system_seek, last_system_scan,

last_system_lookup, last_system_update

FROM sys.dm_db_index_usage_stats

WHERE object_id = object_id(@IndexinfoFor)

SELECT mic.*, mid.Equality_columns, mid.Inequality_columns, mid.Included_columns, mid.Statement,

migs.Unique_Compiles, migs.User_Seeks, migs.User_Scans, migs.Last_User_Seek,

migs.Avg_Total_User_Cost, migs.Avg_User_Impact, migs.System_Scans,

migs.System_Seeks, migs.Last_System_Scan, migs.Last_System_Seek,

migs.Avg_total_system_Cost, migs.Avg_system_Impact

FROM sys.dm_db_index_usage_stats ius

LEFT OUTER JOIN sys.dm_db_missing_index_details mid

ON ius.object_id = mid.object_id

CROSS APPLY sys.dm_db_missing_index_columns(mid.index_handle) mic

LEFT OUTER JOIN sys.dm_db_missing_index_groups mig

ON mid.index_handle = mig.index_handle

LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs

ON mig.index_group_handle = migs.group_handle

WHERE ius.object_id = object_id(@IndexinfoFor)