Compare Sql Server Table Constraints

Hello friends, after a long period of time I’m back into my blog. As due to continuous projects didn’t get a long stretch to sit with my blog. 

In a BI project I faced a lot challenges for migrating some huge databases which ended up with lot of dependencies on sql server schema and object comparison tools(Redgate Sql Data compare,Sql compare,Xsql) to make my SSIS to execute smoothly. Now I’m in a workshop for synchronizing two databases. In the middle of that I’m sharing some useful sql queries which will be useful for happy coding.

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_GET_ALL_CONSTRAINTS]') 
and objectproperty(id, N'isprocedure') = 1)
DROP PROCEDURE [dbo].[SP_GET_ALL_CONSTRAINTS]
go
CREATE PROC SP_GET_ALL_CONSTRAINTS
(
	@sourceServer nvarchar(1000),
	@targetServer nvarchar(1000)
)
--@targetServer db to be migrated(old db) eg 12.3
--@sourceServer is the new database having all new schemas scripts   eg:12.4
as
SELECT	
	'NEW'				AS CHANGE,
	DatabaseName		AS DATABASENAME,
	TABLE_NAME			AS TABLENAME,
	COLUMN_NAME			AS COLUMNNAME,
	CONSTRAINT_NAME		AS CONSTRAINTNAME,
	'N-A'				AS NEW_CONSTRAINTNAME,
	CONSTRAINT_TYPE		AS CONSTRAINTTYPE, 
	ORDINAL_POSITION	AS ORDINALPOSITION,
	REF_PK_KEY			AS REFERENCEPKKEY,
	ParentTable			AS ParentTable,
	ParentField			AS ParentField,
	TYPE_CLUSTER		AS TYPECLUSTER,
	CASE CONSTRAINT_TYPE WHEN 'PRIMARY KEY'
			THEN 'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT' + ' ' + CONSTRAINT_NAME + ' PRIMARY KEY (' + COLUMN_NAME + ')' 
			WHEN 'FOREIGN KEY' THEN 
						'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT '+ CONSTRAINT_NAME + ' ' + 
						CONSTRAINT_TYPE +  '(' + REF_PK_KEY + ') REFERENCES ' + ParentTable + '(' + ParentField + ')' 
			ELSE 'ALTER TABLE dbo.' + TABLE_NAME + ' ADD CONSTRAINT ' + CONSTRAINT_NAME + ' UNIQUE (' + COLUMN_NAME  + ')'
			END AS Script
FROM
	tbl_ALL_CONSTRAINTS
WHERE
	DatabASeName = @targetServer
AND
	CONSTRAINT_NAME NOT IN
	(
		SELECT 	CONSTRAINT_NAME
		FROM 	tbl_ALL_CONSTRAINTS
		WHERE	DatabaseName = @sourceServer
		)
UNION ALL
-- select the changed columns
SELECT	
	'MODIFIED'			AS CHANGE,
	v1.DatabaseName		AS DATABASENAME,
	v1.TABLE_NAME		AS TABLENAME,
	v1.COLUMN_NAME		AS COLUMNNAME,
	v1.CONSTRAINT_NAME	AS CONSTRAINTNAME,
	v2.CONSTRAINT_NAME	AS NEW_CONSTRAINTNAME,
	v1.CONSTRAINT_TYPE	AS CONSTRAINTTYPE, 
	v1.ORDINAL_POSITION AS ORDINALPOSITION,
	v1.REF_PK_KEY		AS REFERENCEPKKEY,
	v1.ParentTable		AS ParentTable,
	v1.ParentField		AS ParentField,
	v1.TYPE_CLUSTER		AS TYPECLUSTER,
	CASE v1.CONSTRAINT_TYPE WHEN 'PRIMARY KEY'
			THEN 'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT  ' 
					+ v1.CONSTRAINT_NAME  + ' ALTER TABLE dbo.' + v1.TABLE_NAME 
					+ ' ADD CONSTRAINT' + ' ' + v2.CONSTRAINT_NAME 
					+ ' PRIMARY KEY (' + v1.COLUMN_NAME + ')' 
			WHEN 'FOREIGN KEY' THEN 
						'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT ' + v1.CONSTRAINT_NAME  +  ' ALTER TABLE dbo.' + v1.TABLE_NAME 
						+ ' ADD CONSTRAINT '+ v2.CONSTRAINT_NAME + ' ' + v1.CONSTRAINT_TYPE  + ' (' + v1.COLUMN_NAME + ') REFERENCES ' 
						+ v1.ParentTable + '(' + v1.ParentField + ')'
			ELSE 'ALTER TABLE dbo.' + v1.TABLE_NAME + ' DROP CONSTRAINT  ' 
					+ v1.CONSTRAINT_NAME + ' ALTER TABLE dbo.' + v1.TABLE_NAME + ' ADD CONSTRAINT ' + v1.CONSTRAINT_NAME + ' UNIQUE (' + v1.COLUMN_NAME  + ')'
			END AS Script
FROM 	
	tbl_ALL_CONSTRAINTS v1
JOIN
	tbl_ALL_CONSTRAINTS v2
ON
	v1.TABLE_NAME  = v2.TABLE_NAME
AND 
	v1.COLUMN_NAME=v2.COLUMN_NAME
AND
	v1.CONSTRAINT_TYPE=v2.CONSTRAINT_TYPE
AND	
	v1.DatabaseName = @sourceServer
AND	
	v2.DatabaseName = @targetServer
AND 
	v1.CONSTRAINT_NAME<>v2.CONSTRAINT_NAME
GO
SET ANSI_NULLS ON 
GO
	

The below script will populate all the sql server metadata information

----QUERY to Find out All the Constraints in Detailed
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CreateAllConstraintsInTable]') and objectproperty(id, N'isprocedure') = 1)
drop procedure [dbo].[sp_CreateAllConstraintsInTable]
go
CREATE PROCEDURE sp_CreateAllConstraintsInTable 
@sourceServer  nvarchar(30), --Database to be compared (New Database)  
@targetServer  nvarchar(30)  --Database to be compared with(Old Database)  
as 
declare @sql varchar(max)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tbl_ALL_CONSTRAINTS]') and objectproperty(id, N'istable') = 1)
drop table [dbo].[tbl_ALL_CONSTRAINTS]

select @sql=

'	SELECT 
	u.TABLESCHEMA,
	u.DatabaseName,
	u.TABLE_NAME,
	u.COLUMN_NAME,
	u.CONSTRAINT_NAME,
	u.CONSTRAINT_TYPE,
	u.ORDINAL_POSITION,
	u.REF_PK_KEY,
	u.ParentTable,
	u.ParentField,
	u.TYPE_CLUSTER into tbl_ALL_CONSTRAINTS
	FROM 
	(
		(SELECT 
			TAB_CON.CONSTRAINT_SCHEMA AS TABLESCHEMA,
			TAB_CON.TABLE_CATALOG as DatabaseName,
			TAB_CON.TABLE_NAME AS TABLE_NAME,
			KEY_COL.COLUMN_NAME AS COLUMN_NAME, 
			TAB_CON.CONSTRAINT_NAME AS CONSTRAINT_NAME,
			TAB_CON.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
			KEY_COL.ORDINAL_POSITION AS ORDINAL_POSITION,
			RC.UNIQUE_CONSTRAINT_NAME  AS REF_PK_KEY,
			ccu.table_name AS ParentTable, 
			ccu.column_name as ParentField, 
			ISNULL(CLUST_TYPE.TYPE_DESC,'+ '''NOT APPLICABLE'''+') AS TYPE_CLUSTER 
		FROM '+
			@sourceServer+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TAB_CON 
		INNER JOIN 
			 '+@sourceServer+'.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KEY_COL  
		ON 
			TAB_CON.TABLE_SCHEMA =  KEY_COL.TABLE_SCHEMA  
		AND 
			TAB_CON.TABLE_NAME = KEY_COL.TABLE_NAME   
		AND 
			TAB_CON.CONSTRAINT_NAME = KEY_COL.CONSTRAINT_NAME  
		LEFT JOIN 
			 '+@sourceServer+'.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC  
		ON 
			RC.CONSTRAINT_NAME =  TAB_CON.CONSTRAINT_NAME
		AND 
			TAB_CON.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA  
		LEFT JOIN  '+@sourceServer+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		ON 
			RC.unique_constraint_schema = ccu.constraint_schema 
		AND 
			RC.unique_constraint_catalog = ccu.constraint_catalog 
		AND 
			RC.unique_constraint_name = ccu.constraint_name  
		LEFT JOIN  
			(SELECT IDX.OBJECT_ID,IDX.NAME,IDX.TYPE_DESC,SCHEMA_ID FROM SYS.INDEXES IDX JOIN SYS.OBJECTS OBJ ON IDX.[OBJECT_ID] = OBJ.[OBJECT_ID] ) 
		AS 
			CLUST_TYPE ON OBJECT_NAME(CLUST_TYPE.OBJECT_ID) = TAB_CON.TABLE_NAME 
		AND 
			CLUST_TYPE.NAME = TAB_CON.CONSTRAINT_NAME  
		AND 
			SCHEMA_NAME( CLUST_TYPE.[SCHEMA_ID]) = TAB_CON.CONSTRAINT_SCHEMA  
			)
			union all
		(SELECT 
			TAB_CON.CONSTRAINT_SCHEMA AS TABLESCHEMA,
			TAB_CON.TABLE_CATALOG as DatabaseName,
			TAB_CON.TABLE_NAME AS TABLE_NAME,
			KEY_COL.COLUMN_NAME AS COLUMN_NAME, 
			TAB_CON.CONSTRAINT_NAME AS CONSTRAINT_NAME,
			TAB_CON.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
			KEY_COL.ORDINAL_POSITION AS ORDINAL_POSITION,
			RC.UNIQUE_CONSTRAINT_NAME  AS REF_PK_KEY,
			ccu.table_name AS ParentTable, 
			ccu.column_name as ParentField, 
			ISNULL(CLUST_TYPE.TYPE_DESC,'+ '''NOT APPLICABLE'''+') AS TYPE_CLUSTER 
		FROM  
			 '+@targetServer+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TAB_CON 
		INNER JOIN 
			'+@targetServer+'.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KEY_COL  
		ON 
			TAB_CON.TABLE_SCHEMA =  KEY_COL.TABLE_SCHEMA  
		AND 
			TAB_CON.TABLE_NAME = KEY_COL.TABLE_NAME   
		AND 
			TAB_CON.CONSTRAINT_NAME = KEY_COL.CONSTRAINT_NAME  
		LEFT JOIN 
			'+@targetServer+'.INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC  
		ON 
			RC.CONSTRAINT_NAME =  TAB_CON.CONSTRAINT_NAME
		AND 
			TAB_CON.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA  
		LEFT JOIN '+@targetServer+'.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		ON 
			RC.unique_constraint_schema = ccu.constraint_schema 
		AND 
			RC.unique_constraint_catalog = ccu.constraint_catalog 
		AND 
			RC.unique_constraint_name = ccu.constraint_name  
		LEFT JOIN  
			(SELECT IDX.OBJECT_ID,IDX.NAME,IDX.TYPE_DESC,SCHEMA_ID FROM SYS.INDEXES IDX JOIN SYS.OBJECTS OBJ ON IDX.[OBJECT_ID] = OBJ.[OBJECT_ID] ) 
		AS 
			CLUST_TYPE ON OBJECT_NAME(CLUST_TYPE.OBJECT_ID) = TAB_CON.TABLE_NAME 
		AND 
			CLUST_TYPE.NAME = TAB_CON.CONSTRAINT_NAME  
		AND 
			SCHEMA_NAME( CLUST_TYPE.[SCHEMA_ID]) = TAB_CON.CONSTRAINT_SCHEMA  
		)
	) u
	ORDER BY 
		u.TABLESCHEMA, u.CONSTRAINT_TYPE DESC, 
		u.TABLE_NAME,u.ORDINAL_POSITION'
exec (@sql)
go

Get this function also from here

Restoring backup of Partitioned Database in sql server 2008

If we need to restore a database having some 'n' number of partition its very tedious to give all the partitioned filegroup to restore into a particular location in the database server.The below example we demonstrate to perform the task.Run the script in the master database.

IF Exists (SELECT '1' from sysobjects where name = 'RESTOREBKP_2008' and type ='p')
BEGIN
DROP PROCEDURE dbo.RESTOREBKP_2008
END
GO
CREATE PROCEDURE RESTOREBKP_2008
(
@DBName varchar(max), -- DATABASENAME
@BackupPath varchar(max), -- BACKUPFILEPATH
@Path varchar(max) -- PHYSICALPATH
)
AS
BEGIN

DECLARE
--Variables declaration
@ResFilesOnly Nvarchar(max),
@ResQuery Nvarchar(max),
@STR AS nVARCHAR(MAX),
@Logicalname as nvarchar(max),
@physicalname as nvarchar(max)

--- LOOP Variable
DECLARE @I AS INT, @J AS INT


set @ResFilesOnly='RESTORE FILELISTONLY
FROM DISK = '''@BackupPath''''

set @I = 1

DECLARE @RESTORE AS TABLE(
LogicalName nvarchar(128), PhysicalName nvarchar(500), Type char(1), FileGroupName nvarchar(128),
Size numeric(20,0), MaxSize numeric(38,0), FileID bigint, CreateLSN numeric(38,0),
DropLSN numeric(25,0) NULL, UniqueID uniqueidentifier, ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL, BackupSizeInBytes bigint, SourceBlockSize int,
FileGroupID int, LogGroupGUID uniqueidentifier NULL, DifferentialBaseLSN numeric(38,0) NULL,
DifferentialBaseGUID uniqueidentifier, IsReadOnly bit, IsPresent bit,TDEThumbprint varchar(500)
)
INSERT INTO @RESTORE
EXEC sp_executesql @ResFilesOnly

SELECT @J = MAX(FILEID) FROM @RESTORE
SET @STR = ''
WHILE @I <= @J
BEGIN
SELECT
@Logicalname = LTRIM(RTRIM(Logicalname)), @physicalname = LTRIM(RTRIM(REVERSE(LEFT(REVERSE(physicalname),CHARINDEX('\',REVERSE(physicalname))-1))))
FROM
@RESTORE
WHERE
FILEID = @I
SET @STR = @STR + 'MOVE N''' + @Logicalname + ''' TO N''' + @Path + @physicalname + ''', '

SET @I = @I + 1
END

--Restore Backup Query
set @ResQuery = 'RESTORE DATABASE ' + @dbname + ' FROM DISK = '''+ @BackupPath + ''' WITH '+ @STR +
N' NOUNLOAD, STATS = 10'

EXECUTE SP_EXECUTESQL @ResQuery
END

Execute this Stored Procedure in your master database as given below

EXEC RESTOREBKP_2008 '', '', ''

Note:-In case of any clarification please mail me to mail@renjucool.com

See this article in MSDN by me http://code.msdn.microsoft.com/Sql2008PartionedDb

Table Comparison in Sql Server

SQL Server is having a command line tool (TableDiff) to compare the data in two tables.It will perform the following task.

1. A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
   
2. Perform a fast comparison by only comparing row counts and schema.
   
3. Perform column-level comparisons.
   
4. Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
   
5. Log results to an output file or into a table in the destination database.

eg:

 

 "C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe" -sourceserver "SERVERNAME" -sourcedatabase "DBNAME" -sourceschema "dbo" -sourcetable "SOURCETABLE1" -sourceuser "sa" -sourcepassword "PASSWORD" -destinationserver "SERVERNAME" -destinationdatabase "DBNAME" -destinationschema "dbo" -destinationtable "SOURCETABLE2" -destinationuser "sa" -destinationpassword "PASSWORD" -dt -o "C:\Documents and Settings\renjuraj\My Documents\diff.xls"

 

Run the above command in command prompt.Please refer your sql server installation path(C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe).

Longest Running Procedures in Sql Server

Its useful to determine which SProc/Code have the greatest impact on the Server. Sometimes, that is determined by examining the I/O cost,sometimes by the Exectution Duration. In this eg, Total Impact is determined by examining the length of execution and the frequency of execution.

SELECT TOP 10 
 temp.text as ProcedureName, 
 s.execution_count as ExecutionCount, 
 isnull( s.total_elapsed_time / s.execution_count, 0 ) as AvgExecutionTime, 
 s.total_worker_time / s.execution_count as AvgWorkerTime, 
 s.total_worker_time as TotalWorkerTime, 
 s.max_logical_reads as MaxLogicalReads, 
 s.max_logical_writes as MaxLogicalWrites, 
 s.creation_time as CreationDateTime, 
 s.total_physical_reads as PhysicalReads,
 isnull( s.execution_count / datediff( second, s.creation_time,getdate()), 0 )as CallsPerSecond
FROM sys.dm_exec_query_stats s 
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) temp 
ORDER BY 
-- s.total_elapsed_time DESC 
s.execution_count desc

Transaction Log growing in Sql Server

Transaction log in SQL Server is one of the most important parts of a SQL Server , as well as one of the most common generators of problems I see online.The following are the causes for transaction log growth,

 

1.Due to Uncommitted transactions. We can find if there are any open transactions using DBCC OPENTRAN.
2.Running DBCC REINDEX, CREATE INDEX operations with the database in Full Recovery model.
3.Running extremely large transactions like Bulk Insert, Select Into commands.
http://support.microsoft.com/kb/317375/

 

The following are the proactive measures in order to minimize the unexpected log file growth,

 

1.If you do not want point in time recovery of your databases then you can change the recovery model to Simple.
2.Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
3.Configure the automatic expansion of transaction log in terms of MB instead of %.
4.Backup the transaction log regularly to delete the inactive transactions in your transaction log if you are using full or bulk logged recovery model.
5.You can switch the recovery model to Bulk logged from full recovery model if you perform some bulk inserts, select into, bcp, alter index, create index commands because these operations will be minimally logged in bulk logged recovery model and after those operations are completed you can switch over to full recovery model.

 

Solutions

1. Take full backup of your database
2. Take t-log backup of your database frequently say every 30 or 15 minutes so that log file will not grow drastically
3. Shrink if you do not have any free space. You can perform this operation manually if required.
4. Generally avoid shrinking the database and keep it as the last option.

If you are in full recovery, then no it won't be truncated.run the following command:

 

SELECT DATABASEPROPERTYEX('Lorenzo347', 'RECOVERY')

If it returns FULL or BULK_LOGGED, then you will have to backup the log, either to disk or specifying the truncate only to get it truncate the space off.  If you do that, then you might as well change your recovery model to SIMPLE and be done with this problem.  If it says you are already in SIMPLE, then it should shrink without problem.

 

backup log <your database name> with truncate_only

 

Shrink Log files

 

DECLARE @LogFileName varchar(100)

SELECT @LogFileName = rtrim(name)

FROM dbo.sysfiles

WHERE Name like '%_log%'

dbcc SHRINKFILE(@LogFileName, 2)

Shrinks the log file  to 2 MB

http://msdn.microsoft.com/en-us/library/aa258824(SQL.80).aspx

Note:Detaching and deleting the log is definitely not advisable   At best it forces you to take your database offline. Worst case is that you invalidate your entire database and have to  restore from backup.

 

Index to be recreated in Sql Server

When your database grows, the index fragmentation becomes too high,that will scale down the performance of the sql server. To overcome that we need to re-build the index, that should be done at the down time of the server.Here there is a query that will list all the index to be recreated/rebuild.It uses the following tables(sys.indexes,sys.tables,sys.schemas,sys.dm_db_index_physical_stats,sys.partitions). For detailed checkup change the last NULL in the dm_db_index_physical_stats call to 'SAMPLED' or even 'DETAILED'

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name +'] ' +
CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD'
ELSE 'REORGANIZE' END +
CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' +
cast(ps.partition_number as nvarchar(max)) ELSE '' END
FROM sys.indexes AS ix INNER JOIN sys.tables t
ON t.object_id = ix.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
INNER JOIN (SELECT object_id, index_id,avg_fragmentation_in_percent, partition_number
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, NULL)) ps
ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT
partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id, index_id) pc
ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent > 10 AND
ix.name IS NOT NULL

Bugs in Sql Server

Today i got noticed a bug in sql server 2005(also in SSMS 08) while executing a simple select statement

select 3 --(*)

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '--(*'.

If we put a space or any other characters before the "(" there is no error. Also
select 3 --)* not returning error.Any way this is a bug in sql server as it does not parse the comment statements



Also i got an error message while Parsing an sql script as

."Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded."

But the script is executing successfully and giving the accurate results!!!!

Last modified Db User Objects & Tables without Trigger in Sql server

If you need to check the details of all DB User Objects by Last Modified Date try the following query


select name, modify_date, case when type_desc = 'USER_TABLE'
then 'Table'when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure'
when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION')
then 'Function'end as type_desc from sys.objects where type in ('U', 'P', 'FN', 'IF', 'TF')and is_ms_shipped = 0
order by 2 desc  
Find the below query that will list all tables without triggers in sql server.This is helpful when someone modify the table
make sure that there aren't any tables that have triggers on them so that stuff doesn't start to break after you make changes.
 
SELECT ob1.name FROM sysobjects ob1 LEFT JOIN sysobjects ob2 ON
ob1.id =ob2.parent_obj AND ob2.xtype = 'TR'
WHERE ob2.name IS NULL AND ob1.xtype = 'U'
ORDER BY ob1.name

Using spt_values to generate junk datas

master..spt_values can be used if you need to populate a table with 100 numbers from 1,or if you need to generate some Junk/Test data.Refer the query below that will generate data of various datatypes

select
abs(checksum(newid()))%10000 as n_id,
abs(checksum(newid()))*rand()/10000 as f_float,
substring(replace(cast(newid() as varchar(36)),'-',''),1,abs(checksum(newid()))%15) as s_string,
dateadd(day,0,abs(checksum(newid()))%100000) as d_date,
abs(checksum(newid()))%2 as b_byte
from master..spt_values
where type='p' and number between 1 and 200

Trigger to monitor the entire database server

Please find this trigger script to monitor the entire database activities and to log its records into audit tables.
It will work on Sql Server 2008.
 
USE [master]
-------Create table -------------------------
-- DROP TABLE AuditTrigger
CREATE TABLE [dbo].[AuditTrigger](
[OID] [bigint] IDENTITY(1,1) NOT NULL,
[Details] [varchar](max) NULL,
[Host] [varchar](20) NULL
)
GO
-----Create proc to insert into AuditTrigger table
IF EXISTS(SELECT 1 FROM sysobjects where name = 'PInsertAuditTrigger')
BEGIN
DROP PROCEDURE PInsertAuditTrigger
END
GO
CREATE PROC PInsertAuditTrigger(@Details varchar(max),@hostname VARCHAR(MAX))
AS
BEGIN
INSERT INTO master.dbo.AuditTrigger(DETAILS,HOST) SELECT @DETAILS,@HOSTNAME
END
-------- Trigger to monitor tables
GO
CREATE TRIGGER [Table_Trigger]
ON ALL SERVER
FOR create_table,alter_table,drop_table
AS
BEGIN
print 'Create/Alter table is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Create/Alter table'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
-------- Trigger to monitor procedures
GO
CREATE TRIGGER [Proc_Trigger]
ON ALL SERVER
FOR create_procedure,alter_procedure,drop_procedure
AS
BEGIN
print 'Create/Alter procedure is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Create/Alter procedure'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
-------- Trigger to monitor login,roles and database
GO
CREATE TRIGGER [Misc_Trigger]
ON ALL SERVER
FOR Create_role,drop_role,alter_role,create_database,drop_database,alter_database,create_view,alter_view,drop_view,create_login,drop_login
AS
BEGIN
print 'Illegal operation is not allowed - '+host_name()
rollback;
DECLARE @host_name varchar(max),@Details varchar(max)
SELECT @host_name = Host_name(),@Details = 'Illegal operation'
exec dbo.PInsertAuditTrigger @Details,@host_name
END
GO

Removing all active connections from the db

Use Master

Go

Declare @dbname sysname

Set @dbname = 'DBNAME'

Declare @spid int Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname)

While @spid Is Not Null Begin

Execute ('Kill ' + @spid)

Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname) and spid > @spid

End

Sql Server 2008-Spacial Data

Microsoft SQL Server 2008 delivers comprehensive spatial support that enables organizations to seamlessly consume, use, and extend location-based data through spatial-enabled applications which ultimately helps end users make better decisions.storing Lat/Long data in a geography datatype and being able to calculate/query using the functions that go along with it.It supports both Planar and Geodetic data.


•Use the new geography data type to store geodetic spatial data and perform operations on it

•Use the new geometry data type to store planar spatial data and perform operations on it

•Take advantage of new spatial indexes for high performance queries

•Use the new spatial results tab to quickly and easily view spatial query results directly from within Management Studio

•Extend spatial data capabilities by building or integrating location-enabled applications through support for spatial standards and specifications

DECLARE @g GEOMETRY
SET @g = 'LINESTRING ( 69 26, 69 23, 69 21, 67 20, 65 20,
63 18, 58 17, 52 17, 51 17, 49 17, 45 18, 44 20,
44 21, 42 26, 42 29, 42 32, 42 35, 43 35, 47 38,
50 41, 55 42, 58 42, 65 44, 66 44, 67 44, 68 45,
69 47, 70 48, 70 50, 71 51, 70 56, 68 60, 68 63,
66 65, 65 66, 63 68, 60 71, 59 71, 57 71, 55 71,
51 69, 45 65, 44 63, 42 62, 41 59, 41 57, 41 56,
41 54, 42 53 ,67 77 ,44 88)'
insert into location values(2,'renju',null,@g);

declare @p geometry
set @p= 'POLYGON ((30 30, 40 30, 40 40, 30 40, 30 30))'
insert into location values(3,'renju2',null,@p);

http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx

Sql Server Hidden Features - Part 2

A lot of SQL Server developers still don't seem to know about the OUTPUT clause (SQL Server 2005 and newer) on the DELETE, INSERT and UPDATE statement.
It can be extremely useful to know which rows have been INSERTed, UPDATEd, or DELETEd, and the OUTPUT clause allows to do this very easily -

it allows access to the "virtual" tables called inserted and deleted (like in triggers):

 

DELETE FROM (table)OUTPUT deleted.ID, deleted.DescriptionWHERE (condition)

 

If you're inserting values into a table which has an INT IDENTITY primary key field, with the OUTPUT clause, you can get the inserted new ID right away:

INSERT INTO MyTable(Field1, Field2)OUTPUT inserted.IDVALUES (Value1, Value2)

 

And if you're updating, it can be extremely useful to know what changed - in this case, inserted represents the new values (after the UPDATE), while deleted refers to the old values before the UPDATE:

 

UPDATE (table)SET field1 = value1, field2 = value2OUTPUT inserted.ID, deleted.field1, inserted.field1WHERE (condition)

If a lot of info will be returned, the output of OUTPUT can also be redirected to a temporary table or a table variable (OUTPUT INTO @myInfoTable).

 

More tips

• Use ctrl-0 to insert a null value in a cell
• WITH (FORCESEEK) which forces the query optimizer to use only an index seek operation as the access path to the data in the table.
  http://msdn.microsoft.com/en-us/library/bb510478.aspx
• PIVOT and UNPIVOT
• Save transactions
• Select xact_state()  --Returns whether transaction pending/committed
• xp_fixeddrives       --Returns the free disk space in the server
• SELECT ASCII('A'),CHAR(65),CHAR(ASCII('A')),UNICODE(N'?'),NCHAR(923),NCHAR(UNICODE(N'?')) --Some Ascii/unicode features
• select * from tablename tablesample(2 percent) --Returns the 2% of the records in the db.Alt select top 10 percent * from table
• select cast(GETDATE()as varchar) as datetime --Returns the date time as formatted.
• WITH TIES
  The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group
• select top 5 with ties * from scores order by name desc
• SELECT Column FROM Table ORDER BY CHECKSUM(NEWID()) -- Return rows in a random order

• master..xp_cmdshell to run commands from a command prompt on the server
  master..xp_cmdshell 'dir d:\'   --List the Directory
  master..xp_cmdshell 'sc query "ccm6.5_ess"' --Query a service
  master..xp_cmdshell 'sc start "ccm6.5_ess"' --Start a service

 

Sql Server Hidden Features

Stored Procedures

* sp_msforeachtable: Runs a command with '?' replaced with each table name (v6.5 and up)
* sp_msforeachdb: Runs a command with '?' replaced with each database name (v7 and up)
* sp_who2: just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up)
* sp_helptext: If you want the code of a stored procedure
* sp_tables: return a list of all tables
* sp_stored_procedures: return a list of all stored procedures
* xp_sscanf: Reads data from the string into the argument locations specified by each format argument.
* xp_fixeddrives:: Find the fixed drive with largest free space
* sp_help: If you want to know the table structure, indexes and constraints of a table
Functions

* HashBytes()
* EncryptByKey
* PIVOT command

TableDiff.exe

* Table Difference tool allows you to discover and reconcile differences between a source and destination table or a view. Tablediff Utility can report differences on schema and data. The most popular feature of tablediff is the fact that it can generate a script that you can run on the destination that will reconcile differences between the tables.
See More

Return rows in a random order
SELECT
SomeColumn
FROM
SomeTable
ORDER BY
CHECKSUM(NEWID())

In Management Studio, you can put a number after a GO end-of-batch marker to cause the batch to be repeated that number of times:

PRINT 'X'
GO 10

Will print 'X' 10 times. This can save you from tedious copy/pasting when doing repetitive stuff

Identity Coloumn Insert

Last week i need a scenario to insert values into a table having an identity coloumn only.

eg: create table test(id int identity)

Is it possible to insert values into the without setting identity insert off/on.I find out a method as follows.

insert into test() default values
go 20

This will insert 20 records to the table haaa...
Will update later..

v$session for oracle and sql server

select * from sys.sysprocesses

can be used for sql server instead of v$session in oracle

If you work with SQL Server or Oracle you at some point probably will need to determine your unique connection identifier. For SQL Server, the connection identifier is called a Server Process ID or SPID. It can be obtained from the global @@SPID as:
SELECT @@SPID
Once the SPID is determined, it can be used to query various system tables (e.g., sysprocesses, sysobjects, etc.) to obtain process information.
For Oracle, the connection identifier is called a Session ID or SID. To get the Session ID, the V$SESSION view must be queried as:

SELECT sid from v$session where audsid = userenv('sessionid');
When the SID is determined, it can be used to find out session information using other Oracle provided system views such as V$SQL.

String Searching in a database

This is the query which searches for string in the entire DB.
Provide 2 inputs as DB name and Search string.
This query takes more time, but will sure get you the results.
You can customize the search criteria…


----------------------------------------------------------
Use [Database_Name]
go
declare @SearchChar varchar(8000)
Set @SearchChar = 'Search_Text' -- Like 'renju%', '11/11/2006'
declare @CMDMain varchar(8000), @CMDMainCount varchar(8000),@CMDJoin varchar(8000)
declare @ColumnName varchar(100),@TableName varchar(100)
declare dbTable cursor for
SELECT
Distinct b.Name as TableName
FROM sysobjects b
WHERE
b.type='u' and b.Name <> 'dtproperties' order by b.name

open dbTable
fetch next from dbTable into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
declare db cursor for
SELECT c.Name as ColumnName
FROM sysobjects b,syscolumns c
WHERE C.id = b.id and
b.type='u' and b.Name = @TableName
order by b.name

open db
fetch next from db into @ColumnName
set @CMDMain = 'SELECT ' + char(39) + @TableName + char(39) + ' as TableName,'+
' ['+ @TableName + '].* FROM [' + @TableName + ']'+
' WHERE '
set @CMDMainCount = 'SELECT Count(*) FROM [' + @TableName + '] Where '
Set @CMDJoin = ''

WHILE @@FETCH_STATUS = 0
BEGIN
set @CMDJoin = @CMDJoin + 'Convert(varchar(5000),[' +@ColumnName + ']) like ' + char(39) + @SearchChar + char(39) + ' OR '
fetch next from db into @ColumnName
end
close db
deallocate db
Set @CMDMainCount = 'If ('+ @CMDMainCount + Left(@CMDJoin, len(@CMDJoin) - 3)+ ') > 0 Begin '
Set @CMDMain = @CMDMainCount + @CMDMain + Left(@CMDJoin, len(@CMDJoin) - 3)
Set @CMDMain = @CMDMain + ' End '
Print @CMDMain
exec (@CMDMain)
fetch next from dbTable into @TableName
end
close dbTable
deallocate dbTable


Renju

Upload T-SQL and execute at your hosting provider using an ASP.NET page

With this approach, you can use the Database Publishing Wizard to generate a T-SQL file from your local database. Then, you can upload the script to your hosting provider, and use the sample ASP.NET page provided to execute the code below.

This approach is useful in the following circumstances:

* Your hosting provider has not deployed the Database Publishing Services, enabling simple publishing of your SQL Server database
* Your hosting provider does not have a T-SQL script execution window or the T-SQL script generated by the Database Publishing Wizard is too large to paste into the T-SQL script execution window

Here is the code, just copy it then paste to your RunSQL.aspx

<%@ Page Language="C#" AutoEventWireup="true"  %>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Import Namespace="System.IO" %>

<%@ Import Namespace="System.Net" %>

<%

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

    // Update these variables here

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

    // Url of the T-SQL file you want to run



    string fileUrl = @"http://<>/<>.sql";    

    

    // Connection string to the server you want to execute against

    string connectionString = @"<>";

    

    // Timeout of batches (in seconds)

    int timeout = 600;

 %>











    





    


    
    

    

    

    <%

        SqlConnection conn = null;                   

        try

        {

            this.Response.Write(String.Format("Opening url {0}
", fileUrl));

            

            // read file

            WebRequest request = WebRequest.Create(fileUrl);

            using (StreamReader sr = new StreamReader(request.GetResponse().GetResponseStream()))

            {

                this.Response.Write("Connecting to SQL Server database...
");

                

                // Create new connection to database

                conn = new SqlConnection(connectionString);               

                

                conn.Open();



                while (!sr.EndOfStream)

                {

                    StringBuilder sb = new StringBuilder();

                    SqlCommand cmd = conn.CreateCommand();

                    

                    while (!sr.EndOfStream)

                    {

                        string s = sr.ReadLine();

                        if (s != null && s.ToUpper().Trim().Equals("GO"))

                        {

                            break;

                        }

                        

                        sb.AppendLine(s);

                    }



                    // Execute T-SQL against the target database

                    cmd.CommandText = sb.ToString();

                    cmd.CommandTimeout = timeout;



                    cmd.ExecuteNonQuery();

                }



            }

            this.Response.Write("T-SQL file executed successfully");

        }

        catch (Exception ex)

        {

            this.Response.Write(String.Format("An error occured: {0}", ex.ToString()));

        }

        finally

        {

            // Close out the connection

            //

            if (conn != null)

            {

                try

                {

                    conn.Close();

                    conn.Dispose();

                }

                catch (Exception e)

                {

                    this.Response.Write(String.Format(@"Could not close the connection.  Error was {0}", e.ToString()));

                }

            }

        }                                 

        %>

Below are the instructions to use this approach:

1. Run the Database Publishing Wizard to generate a T-SQL script file for your local database
2. Using FTP (or another approach if applicable), upload this T-SQL file to your hosting account
3. Download the sample ASP.NET page by clicking on this link: RunSQL.aspx
4. Edit the ASPX page and change the values of the variables fileUrl and connectionString as follows:
1. fileUrl should be the url of the T-SQL file you uploaded. For example if your domain name is www.mydomain.Com, then the url would be http://www.mydomain.com/File.Sql
2. connectionString should be the connection string of your hosted SQL Server database
5. Upload the ASPX page to your hosting account
6. Point your web browser to the ASPX page you uploaded. When this page has completed loading, your database should now be populated in the remote SQL Server database
7. Important: Delete the T-SQL file and ASPX page in your hosting account. This will prevent others from reading your data or tampering with your database.

Stored Procedure using the most CPU

The following query gives you a high-level view of which currently cached batches or procedures are using the most CPU. The query aggregates the CPU consumed by all statements with the same plan__handle (meaning that they are part of the same batch or procedure). If a given plan_handle has more than one statement, you may have to drill in further to find the specific query that is the largest contributor to the overall CPU usage.
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

SCOPE_IDENTITY

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

SCOPE_IDENTITY()

Returns numeric value.eg:- RETURN SCOPE_IDENTITY()

Insert Values into an Identity Column

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted.

use tempdb
GO
IF OBJECT_ID('IdentityTable') IS NOT NULL
DROP TABLE IdentityTable
GO
CREATE TABLE IdentityTable (
TheIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
TheValue NVARCHAR(20) NOT NULL )
GO

Simply trying to INSERT a value into the identity column generates an error:

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (1, 'First Row')
GO

Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'IdentityTable' when IDENTITY_INSERT is set to OFF.

The trick is to enable IDENTITY_INSERT for the table. That looks like this:

SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')

SET IDENTITY_INSERT IdentityTable OFF

Here are some key points about IDENTITY_INSERT

* It can only be enabled on one table at a time. If you try to enable it on a second table while it is still enabled on a first table SQL Server will generate an error.
* When it is enabled on a table you must specify a value for the identity column.
* The user issuing the statement must own the object, be a system administrator (sysadmin role), be the database owner (dbo) or be a member of the db_ddladmin role in order to run the command.

If you insert a value greater than the current identity seed SQL Server uses the value to reset the identity seed. For example:

SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (10, 'Row Ten')

SET IDENTITY_INSERT IdentityTable OFF

INSERT IdentityTable(TheValue)
VALUES ('Should be 11')

SELECT * FROM IdentityTable
GO

(1 row(s) affected)

(1 row(s) affected)
TheIdentity TheValue
----------- --------------------
10 Row Ten
11 Should be 11

(2 row(s) affected)