/************************************
Chapter 13 Scripts
For MCITP SQL Server 2005 Database Developer All-in-One Exam Guide
By Darril Gibson

The scripts available here are the same ones used in each of the chapters. Ideally, you would type in the script and learn from any errors you might make. Occasionally, however, you may want a copy of the script to paste into the query window or into a batch file. If so, feel free to use these script files. 

I strongly encourage you not to rely on these pre-typed scripts, but instead to get used to typing the T-SQL yourself. The more you do it (even if you’re just typing what you see in the book), the more comfortable you’ll get with T-SQL.

Some scripts in the book are intended to be run from the command line. They've been included where appropriate so that you can copy and paste them into Notepad and save them as a batch file (.bat). You can then run the batch file from the command prompt.

Be aware that some of the scripts fail on purpose. For example, the book may be showing how a script works, and then after a constraint is added, the book may show how the same script fails.  The failure is not an error. In other words, just running the scripts without the benefit of the book won't be much help.

*************************************/

EXEC sp_user_counter1 value

/************************************/

USE Master;
GO
CREATE DATABASE Chapter13;
GO
USE Chapter13;
CREATE TABLE dbo.TestTransactions
(
	TestTransactionsID int IDENTITY(1,1) NOT NULL, 
	UserName varchar(50) NOT NULL, 
	UserDate datetime NULL,
    	CONSTRAINT [PK_AuditAccess_AuditAccessID] PRIMARY KEY CLUSTERED 
	(
		TestTransactionsID
	 )
);

/************************************/

ALTER DATABASE Chapter13 
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

/************************************/

DECLARE @intCounter int
DECLARE @myDate datetime
Set @intCounter = 1
While @intCounter < 1000
Begin
  Set @myDate = DATEADD(day, 1, GetDate())
  INSERT INTO TestTransactions VALUES(SUSER_SNAME(),@myDate);
  Set @intCounter = @intCounter + 1
End

/************************************/

Net Send administrator “MCITP Success”

/************************************/

exec sp_addmessage 50100, 16, 'Users cannot be deleted from the database', 'us_english','True','Replace'

/************************************/

raiserror (50100, 16,1)

/************************************/

raiserror (50100, 16,1) WITH LOG
c
exec sp_addmessage 50101, 16, 'Part number %d needs to be reordered.', 'us_english','true','replace'

/************************************/

USE AdventureWorks;
GO
raiserror (50101, 16,1, 55);

/************************************/

BACKUP LOG AdventureWorks 
    TO DISK = 'C:\AW_Log.bak' 

/************************************/

USE AdventureWorks;
GO
DECLARE @i int
SET @i = 1
While @i < 1000 
Begin
  SET @i = @i + 1
      INSERT INTO Person.Contact
      (FirstName, LastName,PasswordHash, PasswordSalt)
      VALUES ('Mickey', 'Mouse', 'P@ssw0rd','P@$$S@lt')
End

/************************************/

DELETE FROM Person.Contact 
WHERE FirstName = 'Mickey' AND LastName = 'Mouse'

/************************************/

USE AdventureWorks;
GO
SELECT * FROM Person.Contact

/************************************/

USE AdventureWorks;
GO
SELECT * FROM Person.Contact

/************************************/

DECLARE @mySQLTrace int
DECLARE @traceid int
EXEC @mySQLTrace=sp_trace_create @traceid=@traceid output,
  @options=0,
  @tracefile=N'C:\MCITPSuccess\myTraceFile'
IF @mySQLTrace > 0 
  PRINT 'sp_trace_code failed with error ' + cast(@mySQLTrace as char)
ELSE
  PRINT 'traceid for the trace is ' + cast(@traceid as char)

/************************************/

DECLARE @on bit
SET @on = 1
EXEC sp_trace_setevent @traceid = 4, -- the number of our trace
   @eventid = 42, --SP_Starting events
   @columnid = 10, -- Application name column
   @on = @on	-- turn it on

/************************************/

DECLARE @on bit
SET @on = 1
EXEC sp_trace_setevent @traceid = 4, -- the number of our trace
   @eventid = 42, --SP_Starting events
   @columnid = 14, -- Start time column
   @on = @on	-- turn it on

/************************************/

sp_trace_setfilter  @traceid = 4, -- the number of our trace
	@columnid = 10, --application name
	@logical_operator = 0, -- 0 is for AND, but with only one filter it doesn't matter
	@comparison_operator = 6, -- 6 is for LIKE
	@value = N'Microsoft SQL Server Management Studio - Query'

/************************************/

sp_trace_setstatus @traceid = 4	-- the number of our trace
          , @status =   1	-- start the trace

/************************************/

USE AdventureWorks;
GO
EXEC dbo.uspGetEmployeeManagers 1

/************************************/

sp_trace_setstatus @traceid = 4	-- the number of our trace
          , @status =   0	-- stop the trace

/************************************/

sp_trace_setstatus @traceid = 4	-- the number of our trace
          , @status =   2	-- delete the trace

/************************************/

USE MASTER;
GO
SELECT * FROM sys.dm_db_index_usage_stats

/************************************/

DECLARE @dbid int
SELECT @dbid = db_id('AdventureWorks')
SELECT objectname=object_name(i.object_id),
       indexname=i.name, i.index_id 
FROM sys.indexes i, sys.objects o 
WHERE objectproperty(o.object_id,'IsUserTable') = 1
AND i.index_id NOT IN 
  (SELECT us.index_id 
   FROM sys.dm_db_index_usage_stats AS us 
   WHERE us.object_id=i.object_id AND
         i.index_id=us.index_id AND database_id = @dbid )
AND o.object_id = i.object_id

/************************************/

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

/************************************/

SELECT * FROM
sys.dm_os_wait_stats 
ORDER BY Wait_time_ms DESC

/************************************/

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

/************************************/

SELECT * FROM
sys.dm_os_wait_stats 
ORDER BY Wait_time_ms DESC

/************************************/

SELECT * FROM sys.dm_os_sys_info

/************************************/

SELECT * FROM sys.dm_db_index_physical_stats
     (DB_ID ('AdventureWorks'), NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent > 10
ORDER BY avg_fragmentation_in_percent DESC

/************************************/

SELECT * FROM sys.objects
WHERE object_id = 1435152158

/************************************/

SELECT name, index_id, index_type_desc, 
  	avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats
     (DB_ID ('AdventureWorks'), NULL, NULL, NULL, NULL) AS S
JOIN sys.objects AS O
ON S.object_ID = O.object_id 
WHERE avg_fragmentation_in_percent > 10
ORDER BY avg_fragmentation_in_percent DESC

/************************************/

USE AdventureWorks;
GO
SELECT * from sys.dm_db_index_usage_stats
WHERE database_id = DB_ID ('AdventureWorks');

/************************************/

USE AdventureWorks;
GO
SELECT * from sys.dm_db_index_usage_stats
WHERE database_id = DB_ID ('AdventureWorks');

/************************************/

USE AdventureWorks;
GO
SELECT * FROM
Person.Contact;

/************************************/

SELECT * from sys.dm_db_index_usage_stats
WHERE database_id = DB_ID ('AdventureWorks');

/************************************/