The following query returns the indexes used on tables in a database. I think it's very useful query for all the sequel server guys.We can use the query result of 'IndexRebuild' column for rebuild index.
SELECT
OBJECT_SCHEMA_NAME(OBJECT_ID) As [Schema],
OBJECT_NAME(OBJECT_ID) [Table],
Name,
'ALTER INDEX ' + name + ' ON '+object_schema_name(OBJECT_ID)+'.'+OBJECT_NAME(OBJECT_ID)+' REBUILD;' As IndexRebuild
,*
FROM sys.indexes
WHERE TYPE > 0
ORDER BY
OBJECT_SCHEMA_NAME(OBJECT_ID),
OBJECT_NAME(OBJECT_ID)
The following system stored procedure returns the index information of a single table.
EXEC sp_helpindex 'TableName'
Thursday, December 29, 2011
Wednesday, December 28, 2011
Date and Time Data Types
| Data type | Format | Range | Accuracy | Storage size (bytes) | User-defined fractional second precision | Time zone offset |
| time | hh:mm:ss[.nnnnnnn] | 00:00:00.0000000 through 23:59:59.9999999 | 100 nanoseconds | 3 to 5 | Yes | No |
| date | YYYY-MM-DD | 0001-01-01 through 9999-12-31 | 1 day | 3 | No | No |
| smalldatetime | YYYY-MM-DD hh:mm:ss | 1900-01-01 through 2079-06-06 | 1 minute | 4 | No | No |
| datetime | YYYY-MM-DD hh:mm:ss[.nnn] | 1753-01-01 through 9999-12-31 | 0.00333 second | 8 | No | No |
| datetime2 | YYYY-MM-DD hh:mm:ss[.nnnnnnn] | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 | 100 nanoseconds | 6 to 8 | Yes | No |
| datetimeoffset | YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm | 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) | 100 nanoseconds | 8 to 10 | Yes | Yes |
Monday, December 26, 2011
GO Command in SQL Server
GO Command indicates the batch ending signal of Transact-SQL statements to the SQL Server utilities.
Syntax:
Syntax:
GO [count]
Is a positive integer. The batch preceding GO will execute the specified number of times.
GO is not a Transact-SQL statement, it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.
Here, i'm giving a tip using GO command to insert same data multiple times wthout a loop.
CREATE TABLE TestTbl
(
id INT IDENTITY(1,1),
name VARCHAR(255),
address VARCHAR(255)
)
SELECT * FROM Northwind..TestTbl
INSERT INTO Northwind..TestTbl VALUES ('johnson','Street view')
GO 5
SELECT * FROM Northwind..TestTbl
How to Enable xp_cmdshell in SQL Server
– To allow advanced options to be changed
EXEC sp_configure 'show advanced options', 1
GO
- To update the currently configured value for advanced options.
RECONFIGURE
GO
- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
- To update the currently configured value for this feature.
RECONFIGURE
GO
Alternative way
1. Click the Start button.
2. Select All Programs.
3. Navigate to the Microsoft SQL Server 2008 folder.
4. Right Click on Server name then click facets options
5. In the Facets Option choose Surface Area Configuration in dropdown
6. At the bottom of the window, mark True for xp_cmdshell.
7. Click OK.
EXEC sp_configure 'show advanced options', 1
GO
- To update the currently configured value for advanced options.
RECONFIGURE
GO
- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
- To update the currently configured value for this feature.
RECONFIGURE
GO
Alternative way
1. Click the Start button.
2. Select All Programs.
3. Navigate to the Microsoft SQL Server 2008 folder.
4. Right Click on Server name then click facets options
5. In the Facets Option choose Surface Area Configuration in dropdown
6. At the bottom of the window, mark True for xp_cmdshell.
7. Click OK.
SQL Server - Detach and Attach
As the name indicates, the detach and attach method for using database disconnect and connect on a sql server system.We can simply detach a database on sql server with ssms interface or using query.But while attaching database on server system,we need to give the mdf and ldf file path with query.Mainly using these method for moving file path of database from one location to other.
We can determine the name and the current location of all files that a database uses by using the
sp_helpfile stored procedure.
use master
go
sp_helpfile
go
-- Detach the database as follows:
use master
go
sp_detach_db 'mydb'
go
Next, copy the data files and the log files from the current location (D:\DataFile\) to the new location (E:\DataFile\).
Re-attach the database. Point to the files in the new location as follows:
use master
go
sp_attach_db 'mydb','E:\DataFile\mydb.mdf','E:\DataFile\mydb.ldf'
go
Verify the change in file locations by using the sp_helpfile stored procedure:
use mydb
go
sp_helpfile
go
The filename column values should reflect the new locations.
We can determine the name and the current location of all files that a database uses by using the
sp_helpfile stored procedure.
use master
go
sp_helpfile
go
-- Detach the database as follows:
use master
go
sp_detach_db 'mydb'
go
Next, copy the data files and the log files from the current location (D:\DataFile\) to the new location (E:\DataFile\).
Re-attach the database. Point to the files in the new location as follows:
use master
go
sp_attach_db 'mydb','E:\DataFile\mydb.mdf','E:\DataFile\mydb.ldf'
go
Verify the change in file locations by using the sp_helpfile stored procedure:
use mydb
go
sp_helpfile
go
The filename column values should reflect the new locations.
Friday, December 23, 2011
Thursday, December 22, 2011
How to get week of given date
In many scenario we need to prepare records weekly basis.Such a cases we want to find week of the given date.I wrote a t-sql tip for to get week of given date.
For Eg , consider 19th november 2011 is given date.The Datepart (wk,<Date>) function will returns the week of the year.So we get result is 47 then take first day (01-Nov-2011) of the given date and applying the same Datepart function formula so we get result 45. Then subtract the result from earlier result with this (47 - 45) after subtraction result add with 1. Finally we get the result is 3. So we can say the 19th november 2011 is in the third week.
Declare @Dt DateTime = '19 nov 2011'
Select Datepart(wk, @Dt) - Datepart(wk, Dateadd(Day, - Day(@Dt) + 1,@Dt)) + 1
For Eg , consider 19th november 2011 is given date.The Datepart (wk,<Date>) function will returns the week of the year.So we get result is 47 then take first day (01-Nov-2011) of the given date and applying the same Datepart function formula so we get result 45. Then subtract the result from earlier result with this (47 - 45) after subtraction result add with 1. Finally we get the result is 3. So we can say the 19th november 2011 is in the third week.
Declare @Dt DateTime = '19 nov 2011'
Select Datepart(wk, @Dt) - Datepart(wk, Dateadd(Day, - Day(@Dt) + 1,@Dt)) + 1
sp_MSforeachdb – Undocumented Stored Procedure
The undocumented stored procedure “sp_MSforeachdb” is found in the “master” database. This is similar to sp_MSforeachtable.
This stored procedure Sp_MSforeachdb gives a DBA the ability to cycle
through every database in your catalog. This stored procedure will loop
every database in your catalog for performing a command.
You can run a DBCC CHECKDB on all databases as below
EXEC sp_Msforeachdb "DBCC checkdb ('?')"
Similarly you can run other command also.
EXEC sp_Msforeachdb "DBCC checkdb ('?')"
Similarly you can run other command also.
sp_MSforeachtable – Undocumented Stored Procedure
As database administrators or
developers, sometimes we need to perform an action on all of the tables
within a database. For example, we may want to disable check constraints
for all tables prior to loading a bunch of data.
No doubt we can use the dynamic statement or cursor for this purpose.
But
we have better alternative way; this is an undocumented stored
procedure called as “sp_MSforeachtable”in the master database. This
stored procedure will loop through all the tables in the database for
performing a command. This stored procedure accepts the following input
parameters.
Example:
Disable all constraints
sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Similarly enable all constraints
sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
Disable all Triggers
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
Similarly enable all Triggers
sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
Delete data from all Table
exec sp_MSforeachtable "delete from ?"
Checks the integrity of each table in the specific database using the DBCC CHECKTABLE command
sp_msforeachtable "dbcc checktable ('?')"
Disable all constraints
sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
Similarly enable all constraints
sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
Disable all Triggers
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
Similarly enable all Triggers
sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"
Delete data from all Table
exec sp_MSforeachtable "delete from ?"
Checks the integrity of each table in the specific database using the DBCC CHECKTABLE command
sp_msforeachtable "dbcc checktable ('?')"
Wednesday, December 21, 2011
How to Disable Foreign Keys and Triggers
Sometimes it's just necessary to get rid of foreign key constraints and triggers in order to do maintenance on a database. Imagine trying to delete all records from 100+ tables, but you now have to put them in dependency-order in order to do it. While foreign key constraints help maintain relational integrity, it can be a huge pain in the rear when you are trying to do basic operations. Of course doing this should only be done when you know-for-sure that you won't ruin the overall integrity of the database.
Disable all Constraints on a table
ALTER TABLE yourtable NOCHECK CONSTRAINT ALL
This will disable, but not remove all of your constraints. It will not, however, remove Primary Key and uniqueness constraints. If you want to only remove a specific constraint, you can replace the word ALL with the name of the constraint - no quotes. To re-enable replace NOCHECK with CHECK
Sometimes we need to disable triggers because they just get in our way.
To Enable all Constraints
ALTER TABLE yourtable WITH CHECK
CHECK CONSTRAINT ALL
To disable all triggers on a table:
DISABLE Trigger ALL ON yourtable
Replace the word ALL with a specific trigger name to disable a specific trigger.
Some notes:
1) Trigger enabling/disabling is only available for Sql Server 2005
2) In order to truncate a table, you must DROP foreign key constraints rather than disable them. Use Delete and change your identity seed with DBCC CHECKIDENT if that will solve your problem.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in an indexed view.
Disable all Constraints on a table
ALTER TABLE yourtable NOCHECK CONSTRAINT ALL
This will disable, but not remove all of your constraints. It will not, however, remove Primary Key and uniqueness constraints. If you want to only remove a specific constraint, you can replace the word ALL with the name of the constraint - no quotes. To re-enable replace NOCHECK with CHECK
Sometimes we need to disable triggers because they just get in our way.
To Enable all Constraints
ALTER TABLE yourtable WITH CHECK
CHECK CONSTRAINT ALL
To disable all triggers on a table:
DISABLE Trigger ALL ON yourtable
Replace the word ALL with a specific trigger name to disable a specific trigger.
Some notes:
1) Trigger enabling/disabling is only available for Sql Server 2005
2) In order to truncate a table, you must DROP foreign key constraints rather than disable them. Use Delete and change your identity seed with DBCC CHECKIDENT if that will solve your problem.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE TABLE may not be used on tables participating in an indexed view.
Tuesday, December 20, 2011
How to Encrypt/Decrypt data in SQL Server
Data encryption in SQL Server 2005 can be done either by using password mechanism or by making use of keys and certificates. In this article, we will look at both mechanisms for data encryption for storage, and then the decryption of the encrypted data for information retrieval.
Encryption by PassPhrase
Doing encryption by passphrase is the easiest approach. But let's first create a table in a demo database, say demodata, for employing encryption/decryption. Name the table as 'users', and create four columns-id, name, card_num and encrypt_num in it. The following SQL script creates the table:
Create Table Users
(
id varchar(16) not null primary key ,
name varchar(50) not null,
card_num varchar(50) not null,
encrypt_num varbinary(MAX)
)
Now, with the EncryptByPassPhrase() method we can encrypt the data in card_num column. While using this method we require a “PassPhrase”, which is nothing but a password that is required to encrypt the data. During decryption the same password has to be passed for data retrieval. The syntax for the method is EncryptByPassPhrase('password','data'), where password is the passphrase and data is the information that is to be encrypted. Suppose we have entries for uuid, name and card_num in the users table, we can run the following script to update the table to encrypt the card_num, and store the converted data in the encrypt_num column. The encrypt_num column is of type varbinary, which holds the data that is encrypted from varchar datatype.
use datademo
UPDATE [users] set [encrypt_num] =
EncryptByPassPhrase('password', [card_num])
The above script converts the data from the card_num column and stores the result in encrypt_num column.
During encryption we used the password as the PassPhrase. Now for decryption, we will have to pass the same PassPhrase.
Supplying a different PassPhrase would result in non-retrieval of the result.
Using DecryptByPassPhrase() method, we will decrypt the data in the column encrypt_num to retrieve the original data. The following script shows a new column, which has decrypted the data from the encrypt_num column, and displays that in the new Decrypted Card Num column.
use [datademo]
select
[id],name,
convert(bigint,convert(varchar(100),
decryptbypassphrase('password',[encrypt_num]))) as
[Decrypted Card Num] from [users]
In real scenarios, we would be employing the EncryptByPassPhrase() method directly through data access object codes either in Java or .NET, and we won't be saving original card number in the table as we did here in this article. Similarly, for decryption the DecryptByPassPhrase() method will be done at program level and not at the database level. This mechanism is convenient for data encryption and decryption, but each time we require PassPhrase for both the processes. This means that PassPhrase is still vulnerable, as we have to store it in some procedure for data access objects.
Encryption by PassPhrase
Doing encryption by passphrase is the easiest approach. But let's first create a table in a demo database, say demodata, for employing encryption/decryption. Name the table as 'users', and create four columns-id, name, card_num and encrypt_num in it. The following SQL script creates the table:
Create Table Users
(
id varchar(16) not null primary key ,
name varchar(50) not null,
card_num varchar(50) not null,
encrypt_num varbinary(MAX)
)
Now, with the EncryptByPassPhrase() method we can encrypt the data in card_num column. While using this method we require a “PassPhrase”, which is nothing but a password that is required to encrypt the data. During decryption the same password has to be passed for data retrieval. The syntax for the method is EncryptByPassPhrase('password','data'), where password is the passphrase and data is the information that is to be encrypted. Suppose we have entries for uuid, name and card_num in the users table, we can run the following script to update the table to encrypt the card_num, and store the converted data in the encrypt_num column. The encrypt_num column is of type varbinary, which holds the data that is encrypted from varchar datatype.
 |
| After encrypting the card_num column, we can see the result of encrypted card number by running the select query |
use datademo
UPDATE [users] set [encrypt_num] =
EncryptByPassPhrase('password', [card_num])
The above script converts the data from the card_num column and stores the result in encrypt_num column.
During encryption we used the password as the PassPhrase. Now for decryption, we will have to pass the same PassPhrase.
Supplying a different PassPhrase would result in non-retrieval of the result.
Using DecryptByPassPhrase() method, we will decrypt the data in the column encrypt_num to retrieve the original data. The following script shows a new column, which has decrypted the data from the encrypt_num column, and displays that in the new Decrypted Card Num column.
use [datademo]
select
[id],name,
convert(bigint,convert(varchar(100),
decryptbypassphrase('password',[encrypt_num]))) as
[Decrypted Card Num] from [users]
 | ||||||||||||||||||||
| Decrypted' shows decrypted info from the 'encrypt_num'. The same symmetric key is used for encryption and decryption |
In real scenarios, we would be employing the EncryptByPassPhrase() method directly through data access object codes either in Java or .NET, and we won't be saving original card number in the table as we did here in this article. Similarly, for decryption the DecryptByPassPhrase() method will be done at program level and not at the database level. This mechanism is convenient for data encryption and decryption, but each time we require PassPhrase for both the processes. This means that PassPhrase is still vulnerable, as we have to store it in some procedure for data access objects.
Encryption by Keys
The limitation of encryption by passphrase methods is that we have to supply the password or passphrase each time the data has to be accessed. But, if we encrypt our symmetric key with a certificate then we won't have to pass the passphrase each time. To create a key or its certificate, we must first create or open the master key for the database. The following command creates a master key:
The limitation of encryption by passphrase methods is that we have to supply the password or passphrase each time the data has to be accessed. But, if we encrypt our symmetric key with a certificate then we won't have to pass the passphrase each time. To create a key or its certificate, we must first create or open the master key for the database. The following command creates a master key:
create master key encryption by password = 'password';
Now, we can create a certificate and then a symmetric
key that is attached to that certificate. The following SQL script
creates the certificate 'DemoCert' and a key 'DemoKey' associated with
that certificate.
create certificate DemoCert with subject = 'Demo Certificate';
create symmetric key DemoKey with
algorithm=AES_256 encryption by certificate DemoCert;
algorithm=AES_256 encryption by certificate DemoCert;
Now that we possess a key, we can do encryption using
the EncryptByKey() method. First of all delete contents from the
encrypt_num column in the 'users' table that we earlier used for storing
the encrypted data of the column card_num. Once we have deleted the
contents, we can again encrypt the data from the card_num column and
store the result in encrypt_num column by using the EncryptByKey()
method.
open symmetric key DemoKey decryption by certificate DemoCert;
use datademo
update [users] set [encrypt_num] =
EncryptByKey(Key_GUID('DemoKey'), card_num)
use datademo
update [users] set [encrypt_num] =
EncryptByKey(Key_GUID('DemoKey'), card_num)
The above script opens up the symmetric key 'DemoKey'
that is associated with the certificate DemoCert. While updating the
column for encryption we pass the same key as a parameter to the
Key_GUID variable, which is one of the parameters of the EncryptByKey()
method. Now while decrypting, all you have to do is to open a session
for the key DemoKey, and decrypt the encrypted data. Same key is needed
for the encryption and the corresponding decryption process.
open symmetric key DemoKey decryption by certificate DemoCert;
use datademo
select ID,
encrypt_num,
cast(DecryptByKey(encrypt_num)
as varchar(16)) as "Decrypted"
from users;
use datademo
select ID,
encrypt_num,
cast(DecryptByKey(encrypt_num)
as varchar(16)) as "Decrypted"
from users;
This is a better method, as we do not have to pass
the password for the process of encryption/decryption. All we have to do
is to add an 'open symmetric key' phrase to each data manipulation
command while programming, i.e. to each update, insert, or select
statement. The key remains open till we explicitly close it. Here also,
in real world scenario, we won't be saving any original data in the
database table, but will be saving the encrypted data by use of keys.
The DB admin may have created these keys.
Data encryption is the key to data security. With SQL
Server 2005 you can secure your data by using the encryption or
decryption functions that it provides.
Compare Two SQL Server Databases
This Stored Procedure compares the structures of two databases. Compares
if all tables in one database have an analog (by name) in the second
database and also compares the structures of the tables.
USE Master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_CompareDB' AND TYPE = 'P')
DROP PROC sp_CompareDB
GO
--------------------------------------------------------------------------------------------
-- sp_CompareDB 'Database1', 'Database2'
--
-- The SP compares structures in 2 databases.
-- 1. Compares if all tables in one database have analog (by name) in second database
-- Tables not existing in one of databases won't be used for data comparing
-- 2. Compares if structures for tables with the same names are the same. Shows structural
-- differences like:
-- authors
-- Column Phone: in db1 - char(12), in db2 - char(14)
-- sales
-- Column Location not in db2
-- Tables, having different structures, won't be used for data comparing. However if the tables
-- contain columns of the same type and different length (like Phone in the example above) or
-- tables have compatible data types (have the same type in syscolumns - char and nchar,
-- varchar and nvarchar etc) they will be allowed for data comparing.
--------------------------------------------------------------------------------------------
-- Parameters:
-- 1. @db1 - name of first database to compare
-- 2. @db2 - name of second database to compare
-- 3. @TabList - list of tables to compare. if empty - all tables in the databases should be
-- compared
-- 4. @NumbToShow - number of rows with differences to show. Default - 10.
-- 5. @OnlyStructure - flag, if set to 1, allows to avoid data comparing. Only structures should
-- be compared. Default - 0
-- 6. @NoTimestamp - flag, if set to 1, allows to avoid comparing of columns of timestamp
-- data type. Default - 0
-- 7. @VerboseLevel - if set to 1 allows to print queries used for data comparison
--------------------------------------------------------------------------------------------
CREATE PROC sp_CompareDB
(
@db1 VARCHAR(128),
@db2 VARCHAR(128),
@OnlyStructure BIT = 0,
@TabList VARCHAR(8000) = '',
@NumbToShow INT = 10,
@NoTimestamp BIT = 0,
@VerboseLevel TINYINT = 0
)
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
IF @OnlyStructure <> 0 SET @OnlyStructure = 1
IF @NoTimestamp <> 0 SET @NoTimestamp = 1
IF @VerboseLevel <> 0 SET @VerboseLevel = 1
DECLARE @sqlStr VARCHAR(8000)
-- Checking if there are specified databases
DECLARE @SrvName sysname
DECLARE @DBName sysname
SET @db1 = RTRIM(LTRIM(@db1))
SET @db2 = RTRIM(LTRIM(@db2))
SET @SrvName = @@SERVERNAME
IF CHARINDEX('.',@db1) > 0
BEGIN
SET @SrvName = LEFT(@db1,CHARINDEX('.',@db1)-1)
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = @SrvName)
BEGIN
PRINT 'There is no linked server named '+@SrvName+'. End of work.'
RETURN
END
SET @DBName = RIGHT(@db1,LEN(@db1)-CHARINDEX('.',@db1))
END
ELSE
SET @DBName = @db1
EXEC ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'There is no database named '+@db1+'. End of work.'
RETURN
END
SET @SrvName = @@SERVERNAME
IF CHARINDEX('.',@db2) > 0
BEGIN
SET @SrvName = LEFT(@db2,CHARINDEX('.',@db2)-1)
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = @SrvName)
BEGIN
PRINT 'There is no linked server named '+@SrvName+'. End of work.'
RETURN
END
SET @DBName = RIGHT(@db2,LEN(@db2)-CHARINDEX('.',@db2))
END
ELSE
SET @DBName = @db2
EXEC ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'There is no database named '+@db2+'. End of work.'
RETURN
END
PRINT REPLICATE('-',LEN(@db1)+LEN(@db2)+25)
PRINT 'Comparing databases '+@db1+' and '+@db2
PRINT REPLICATE('-',LEN(@db1)+LEN(@db2)+25)
PRINT 'Options specified:'
PRINT ' Compare only structures: '+CASE WHEN @OnlyStructure = 0 THEN 'No' ELSE 'Yes' END
PRINT ' List of tables to compare: '+CASE WHEN LEN(@TabList) = 0 THEN ' All tables' ELSE @TabList END
PRINT ' Max number of different rows in each table to show: '+LTRIM(STR(@NumbToShow))
PRINT ' Compare timestamp columns: '+CASE WHEN @NoTimestamp = 0 THEN 'No' ELSE 'Yes' END
PRINT ' Verbose level: '+CASE WHEN @VerboseLevel = 0 THEN 'Low' ELSE 'High' END
-----------------------------------------------------------------------------------------
-- Comparing structures
-----------------------------------------------------------------------------------------
PRINT CHAR(10)+REPLICATE('-',36)
PRINT 'Comparing structure of the databases'
PRINT REPLICATE('-',36)
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#TabToCheck%')
DROP TABLE #TabToCheck
CREATE TABLE #TabToCheck (name sysname)
DECLARE @NextCommaPos INT
IF LEN(@TabList) > 0
BEGIN
WHILE 1=1
BEGIN
SET @NextCommaPos = CHARINDEX(',',@TabList)
IF @NextCommaPos = 0
BEGIN
SET @sqlstr = 'insert into #TabToCheck values('''+@TabList+''')'
EXEC (@sqlstr)
BREAK
END
SET @sqlstr = 'insert into #TabToCheck values('''+LEFT(@TabList,@NextCommaPos-1)+''')'
EXEC (@sqlstr)
SET @TabList = RIGHT(@TabList,LEN(@TabList)-@NextCommaPos)
END
END
ELSE -- then will check all tables
BEGIN
EXEC ('insert into #TabToCheck select name from '+@db1+'.dbo.sysobjects where type = ''U''')
EXEC ('insert into #TabToCheck select name from '+@db2+'.dbo.sysobjects where type = ''U''')
END
-- First check if at least one table specified in @TabList exists in db1
EXEC ('declare @Name sysname select @Name=name from '+@db1+'.dbo.sysobjects where name in (select * from #TabToCheck)')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'No tables in '+@db1+' to check. End of work.'
RETURN
END
-- Check if tables existing in db1 are in db2 (all tables or specified in @TabList)
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#TabNotInDB2%')
DROP TABLE #TabNotInDB2
CREATE TABLE #TabNotInDB2 (name sysname)
INSERT INTO #TabNotInDB2
EXEC ('select name from '+@db1+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@db2+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name) order by name')
IF @@ROWCOUNT > 0
BEGIN
PRINT CHAR(10)+'The table(s) exist in '+@db1+', but do not exist in '+@db2+':'
SELECT * FROM #TabNotInDB2
END
DELETE FROM #TabToCheck WHERE name IN (SELECT * FROM #TabNotInDB2)
DROP TABLE #TabNotInDB2
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#TabNotInDB1%')
DROP TABLE #TabNotInDB1
CREATE TABLE #TabNotInDB1 (name sysname)
INSERT INTO #TabNotInDB1
EXEC ('select name from '+@db2+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@db1+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name) order by name')
IF @@ROWCOUNT > 0
BEGIN
PRINT CHAR(10)+'The table(s) exist in '+@db2+', but do not exist in '+@db1+':'
SELECT * FROM #TabNotInDB1
END
DELETE FROM #TabToCheck WHERE name IN (SELECT * FROM #TabNotInDB1)
DROP TABLE #TabNotInDB1
-- Comparing structures of tables existing in both dbs
PRINT CHAR(10)+'Checking if there are tables existing in both databases having structural differences ...'+CHAR(10)
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#DiffStructure%')
DROP TABLE #DiffStructure
CREATE TABLE #DiffStructure (name sysname)
SET @sqlStr='
declare @TName1 sysname, @TName2 sysname, @CName1 sysname, @CName2 sysname,
@TypeName1 sysname, @TypeName2 sysname,
@CLen1 smallint, @CLen2 smallint, @Type1 sysname, @Type2 sysname, @PrevTName sysname
declare @DiffStructure bit
declare Diff cursor fast_forward for
select d1o.name, d2o.name, d1c.name, d2c.name, d1t.name, d2t.name,
d1c.length, d2c.length, d1c.type, d2c.type
from ('+@db1+'.dbo.sysobjects d1o
JOIN '+@db2+'.dbo.sysobjects d2o2 ON d1o.name = d2o2.name and d1o.type = ''U'' --only tables in both dbs
and d1o.name in (select * from #TabToCheck)
JOIN '+@db1+'.dbo.syscolumns d1c ON d1o.id = d1c.id
JOIN '+@db1+'.dbo.systypes d1t ON d1c.xusertype = d1t.xusertype)
FULL JOIN ('+@db2+'.dbo.sysobjects d2o
JOIN '+@db1+'.dbo.sysobjects d1o2 ON d1o2.name = d2o.name and d2o.type = ''U'' --only tables in both dbs
and d2o.name in (select * from #TabToCheck)
JOIN '+@db2+'.dbo.syscolumns d2c ON d2c.id = d2o.id
JOIN '+@db2+'.dbo.systypes d2t ON d2c.xusertype = d2t.xusertype)
ON d1o.name = d2o.name and d1c.name = d2c.name
WHERE (not exists
(select * from '+@db2+'.dbo.sysobjects d2o2
JOIN '+@db2+'.dbo.syscolumns d2c2 ON d2o2.id = d2c2.id
JOIN '+@db2+'.dbo.systypes d2t2 ON d2c2.xusertype = d2t2.xusertype
where d2o2.type = ''U''
and d2o2.name = d1o.name
and d2c2.name = d1c.name
and d2t2.name = d1t.name
and d2c2.length = d1c.length)
OR not exists
(select * from '+@db1+'.dbo.sysobjects d1o2
JOIN '+@db1+'.dbo.syscolumns d1c2 ON d1o2.id = d1c2.id
JOIN '+@db1+'.dbo.systypes d1t2 ON d1c2.xusertype = d1t2.xusertype
where d1o2.type = ''U''
and d1o2.name = d2o.name
and d1c2.name = d2c.name
and d1t2.name = d2t.name
and d1c2.length = d2c.length))
order by coalesce(d1o.name,d2o.name), d1c.name
open Diff
fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,
@CLen1, @CLen2, @Type1, @Type2
set @PrevTName = ''''
set @DiffStructure = 0
while @@fetch_status = 0
begin
if Coalesce(@TName1,@TName2) <> @PrevTName
begin
if @PrevTName <> '''' and @DiffStructure = 1
begin
insert into #DiffStructure values (@PrevTName)
set @DiffStructure = 0
end
set @PrevTName = Coalesce(@TName1,@TName2)
print @PrevTName
end
if @CName2 is null
print '' Column ''+RTRIM(@CName1)+'' not in '+@db2+'''
else
if @CName1 is null
print '' Column ''+RTRIM(@CName2)+'' not in '+@db1+'''
else
if @TypeName1 <> @TypeName2
print '' Column ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+'', in '+@db2+' - ''+RTRIM(@TypeName2)
else --the columns are not null(are in both dbs) and types are equal,then length are diff
print '' Column ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+''(''+
LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen1/2 else @CLen1 end))+
''), in '+@db2+' - ''+RTRIM(@TypeName2)+''(''+
LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen2/2 else @CLen2 end))+'')''
if @Type1 = @Type2
set @DiffStructure=@DiffStructure -- Do nothing. Cannot invert predicate
else
set @DiffStructure = 1
fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,
@CLen1, @CLen2, @Type1, @Type2
end
deallocate Diff
if @DiffStructure = 1
insert into #DiffStructure values (@PrevTName)
'
EXEC (@sqlStr)
IF (SELECT COUNT(*) FROM #DiffStructure) > 0
BEGIN
PRINT CHAR(10)+'The table(s) have the same name and different structure in the databases:'
SELECT DISTINCT * FROM #DiffStructure
DELETE FROM #TabToCheck WHERE name IN (SELECT * FROM #DiffStructure)
END
ELSE
PRINT CHAR(10)+'There are no tables with the same name and structural differences in the databases'+CHAR(10)+CHAR(10)
IF @OnlyStructure = 1
BEGIN
PRINT 'The option ''Only compare structures'' was specified. End of work.'
RETURN
END
EXEC ('declare @Name sysname select @Name=d1o.name
from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'There are no tables with the same name and structure in the databases to compare. End of work.'
RETURN
END
END
USE Master
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_CompareDB' AND TYPE = 'P')
DROP PROC sp_CompareDB
GO
--------------------------------------------------------------------------------------------
-- sp_CompareDB 'Database1', 'Database2'
--
-- The SP compares structures in 2 databases.
-- 1. Compares if all tables in one database have analog (by name) in second database
-- Tables not existing in one of databases won't be used for data comparing
-- 2. Compares if structures for tables with the same names are the same. Shows structural
-- differences like:
-- authors
-- Column Phone: in db1 - char(12), in db2 - char(14)
-- sales
-- Column Location not in db2
-- Tables, having different structures, won't be used for data comparing. However if the tables
-- contain columns of the same type and different length (like Phone in the example above) or
-- tables have compatible data types (have the same type in syscolumns - char and nchar,
-- varchar and nvarchar etc) they will be allowed for data comparing.
--------------------------------------------------------------------------------------------
-- Parameters:
-- 1. @db1 - name of first database to compare
-- 2. @db2 - name of second database to compare
-- 3. @TabList - list of tables to compare. if empty - all tables in the databases should be
-- compared
-- 4. @NumbToShow - number of rows with differences to show. Default - 10.
-- 5. @OnlyStructure - flag, if set to 1, allows to avoid data comparing. Only structures should
-- be compared. Default - 0
-- 6. @NoTimestamp - flag, if set to 1, allows to avoid comparing of columns of timestamp
-- data type. Default - 0
-- 7. @VerboseLevel - if set to 1 allows to print queries used for data comparison
--------------------------------------------------------------------------------------------
CREATE PROC sp_CompareDB
(
@db1 VARCHAR(128),
@db2 VARCHAR(128),
@OnlyStructure BIT = 0,
@TabList VARCHAR(8000) = '',
@NumbToShow INT = 10,
@NoTimestamp BIT = 0,
@VerboseLevel TINYINT = 0
)
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
IF @OnlyStructure <> 0 SET @OnlyStructure = 1
IF @NoTimestamp <> 0 SET @NoTimestamp = 1
IF @VerboseLevel <> 0 SET @VerboseLevel = 1
DECLARE @sqlStr VARCHAR(8000)
-- Checking if there are specified databases
DECLARE @SrvName sysname
DECLARE @DBName sysname
SET @db1 = RTRIM(LTRIM(@db1))
SET @db2 = RTRIM(LTRIM(@db2))
SET @SrvName = @@SERVERNAME
IF CHARINDEX('.',@db1) > 0
BEGIN
SET @SrvName = LEFT(@db1,CHARINDEX('.',@db1)-1)
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = @SrvName)
BEGIN
PRINT 'There is no linked server named '+@SrvName+'. End of work.'
RETURN
END
SET @DBName = RIGHT(@db1,LEN(@db1)-CHARINDEX('.',@db1))
END
ELSE
SET @DBName = @db1
EXEC ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'There is no database named '+@db1+'. End of work.'
RETURN
END
SET @SrvName = @@SERVERNAME
IF CHARINDEX('.',@db2) > 0
BEGIN
SET @SrvName = LEFT(@db2,CHARINDEX('.',@db2)-1)
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = @SrvName)
BEGIN
PRINT 'There is no linked server named '+@SrvName+'. End of work.'
RETURN
END
SET @DBName = RIGHT(@db2,LEN(@db2)-CHARINDEX('.',@db2))
END
ELSE
SET @DBName = @db2
EXEC ('declare @Name sysname select @Name=name from ['+@SrvName+'].master.dbo.sysdatabases where name = '''+@DBName+'''')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'There is no database named '+@db2+'. End of work.'
RETURN
END
PRINT REPLICATE('-',LEN(@db1)+LEN(@db2)+25)
PRINT 'Comparing databases '+@db1+' and '+@db2
PRINT REPLICATE('-',LEN(@db1)+LEN(@db2)+25)
PRINT 'Options specified:'
PRINT ' Compare only structures: '+CASE WHEN @OnlyStructure = 0 THEN 'No' ELSE 'Yes' END
PRINT ' List of tables to compare: '+CASE WHEN LEN(@TabList) = 0 THEN ' All tables' ELSE @TabList END
PRINT ' Max number of different rows in each table to show: '+LTRIM(STR(@NumbToShow))
PRINT ' Compare timestamp columns: '+CASE WHEN @NoTimestamp = 0 THEN 'No' ELSE 'Yes' END
PRINT ' Verbose level: '+CASE WHEN @VerboseLevel = 0 THEN 'Low' ELSE 'High' END
-----------------------------------------------------------------------------------------
-- Comparing structures
-----------------------------------------------------------------------------------------
PRINT CHAR(10)+REPLICATE('-',36)
PRINT 'Comparing structure of the databases'
PRINT REPLICATE('-',36)
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#TabToCheck%')
DROP TABLE #TabToCheck
CREATE TABLE #TabToCheck (name sysname)
DECLARE @NextCommaPos INT
IF LEN(@TabList) > 0
BEGIN
WHILE 1=1
BEGIN
SET @NextCommaPos = CHARINDEX(',',@TabList)
IF @NextCommaPos = 0
BEGIN
SET @sqlstr = 'insert into #TabToCheck values('''+@TabList+''')'
EXEC (@sqlstr)
BREAK
END
SET @sqlstr = 'insert into #TabToCheck values('''+LEFT(@TabList,@NextCommaPos-1)+''')'
EXEC (@sqlstr)
SET @TabList = RIGHT(@TabList,LEN(@TabList)-@NextCommaPos)
END
END
ELSE -- then will check all tables
BEGIN
EXEC ('insert into #TabToCheck select name from '+@db1+'.dbo.sysobjects where type = ''U''')
EXEC ('insert into #TabToCheck select name from '+@db2+'.dbo.sysobjects where type = ''U''')
END
-- First check if at least one table specified in @TabList exists in db1
EXEC ('declare @Name sysname select @Name=name from '+@db1+'.dbo.sysobjects where name in (select * from #TabToCheck)')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'No tables in '+@db1+' to check. End of work.'
RETURN
END
-- Check if tables existing in db1 are in db2 (all tables or specified in @TabList)
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#TabNotInDB2%')
DROP TABLE #TabNotInDB2
CREATE TABLE #TabNotInDB2 (name sysname)
INSERT INTO #TabNotInDB2
EXEC ('select name from '+@db1+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@db2+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name) order by name')
IF @@ROWCOUNT > 0
BEGIN
PRINT CHAR(10)+'The table(s) exist in '+@db1+', but do not exist in '+@db2+':'
SELECT * FROM #TabNotInDB2
END
DELETE FROM #TabToCheck WHERE name IN (SELECT * FROM #TabNotInDB2)
DROP TABLE #TabNotInDB2
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#TabNotInDB1%')
DROP TABLE #TabNotInDB1
CREATE TABLE #TabNotInDB1 (name sysname)
INSERT INTO #TabNotInDB1
EXEC ('select name from '+@db2+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@db1+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name) order by name')
IF @@ROWCOUNT > 0
BEGIN
PRINT CHAR(10)+'The table(s) exist in '+@db2+', but do not exist in '+@db1+':'
SELECT * FROM #TabNotInDB1
END
DELETE FROM #TabToCheck WHERE name IN (SELECT * FROM #TabNotInDB1)
DROP TABLE #TabNotInDB1
-- Comparing structures of tables existing in both dbs
PRINT CHAR(10)+'Checking if there are tables existing in both databases having structural differences ...'+CHAR(10)
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '#DiffStructure%')
DROP TABLE #DiffStructure
CREATE TABLE #DiffStructure (name sysname)
SET @sqlStr='
declare @TName1 sysname, @TName2 sysname, @CName1 sysname, @CName2 sysname,
@TypeName1 sysname, @TypeName2 sysname,
@CLen1 smallint, @CLen2 smallint, @Type1 sysname, @Type2 sysname, @PrevTName sysname
declare @DiffStructure bit
declare Diff cursor fast_forward for
select d1o.name, d2o.name, d1c.name, d2c.name, d1t.name, d2t.name,
d1c.length, d2c.length, d1c.type, d2c.type
from ('+@db1+'.dbo.sysobjects d1o
JOIN '+@db2+'.dbo.sysobjects d2o2 ON d1o.name = d2o2.name and d1o.type = ''U'' --only tables in both dbs
and d1o.name in (select * from #TabToCheck)
JOIN '+@db1+'.dbo.syscolumns d1c ON d1o.id = d1c.id
JOIN '+@db1+'.dbo.systypes d1t ON d1c.xusertype = d1t.xusertype)
FULL JOIN ('+@db2+'.dbo.sysobjects d2o
JOIN '+@db1+'.dbo.sysobjects d1o2 ON d1o2.name = d2o.name and d2o.type = ''U'' --only tables in both dbs
and d2o.name in (select * from #TabToCheck)
JOIN '+@db2+'.dbo.syscolumns d2c ON d2c.id = d2o.id
JOIN '+@db2+'.dbo.systypes d2t ON d2c.xusertype = d2t.xusertype)
ON d1o.name = d2o.name and d1c.name = d2c.name
WHERE (not exists
(select * from '+@db2+'.dbo.sysobjects d2o2
JOIN '+@db2+'.dbo.syscolumns d2c2 ON d2o2.id = d2c2.id
JOIN '+@db2+'.dbo.systypes d2t2 ON d2c2.xusertype = d2t2.xusertype
where d2o2.type = ''U''
and d2o2.name = d1o.name
and d2c2.name = d1c.name
and d2t2.name = d1t.name
and d2c2.length = d1c.length)
OR not exists
(select * from '+@db1+'.dbo.sysobjects d1o2
JOIN '+@db1+'.dbo.syscolumns d1c2 ON d1o2.id = d1c2.id
JOIN '+@db1+'.dbo.systypes d1t2 ON d1c2.xusertype = d1t2.xusertype
where d1o2.type = ''U''
and d1o2.name = d2o.name
and d1c2.name = d2c.name
and d1t2.name = d2t.name
and d1c2.length = d2c.length))
order by coalesce(d1o.name,d2o.name), d1c.name
open Diff
fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,
@CLen1, @CLen2, @Type1, @Type2
set @PrevTName = ''''
set @DiffStructure = 0
while @@fetch_status = 0
begin
if Coalesce(@TName1,@TName2) <> @PrevTName
begin
if @PrevTName <> '''' and @DiffStructure = 1
begin
insert into #DiffStructure values (@PrevTName)
set @DiffStructure = 0
end
set @PrevTName = Coalesce(@TName1,@TName2)
print @PrevTName
end
if @CName2 is null
print '' Column ''+RTRIM(@CName1)+'' not in '+@db2+'''
else
if @CName1 is null
print '' Column ''+RTRIM(@CName2)+'' not in '+@db1+'''
else
if @TypeName1 <> @TypeName2
print '' Column ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+'', in '+@db2+' - ''+RTRIM(@TypeName2)
else --the columns are not null(are in both dbs) and types are equal,then length are diff
print '' Column ''+RTRIM(@CName1)+'': in '+@db1+' - ''+RTRIM(@TypeName1)+''(''+
LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen1/2 else @CLen1 end))+
''), in '+@db2+' - ''+RTRIM(@TypeName2)+''(''+
LTRIM(STR(CASE when @TypeName1=''nChar'' or @TypeName1 = ''nVarChar'' then @CLen2/2 else @CLen2 end))+'')''
if @Type1 = @Type2
set @DiffStructure=@DiffStructure -- Do nothing. Cannot invert predicate
else
set @DiffStructure = 1
fetch next from Diff into @TName1, @TName2, @CName1, @CName2, @TypeName1, @TypeName2,
@CLen1, @CLen2, @Type1, @Type2
end
deallocate Diff
if @DiffStructure = 1
insert into #DiffStructure values (@PrevTName)
'
EXEC (@sqlStr)
IF (SELECT COUNT(*) FROM #DiffStructure) > 0
BEGIN
PRINT CHAR(10)+'The table(s) have the same name and different structure in the databases:'
SELECT DISTINCT * FROM #DiffStructure
DELETE FROM #TabToCheck WHERE name IN (SELECT * FROM #DiffStructure)
END
ELSE
PRINT CHAR(10)+'There are no tables with the same name and structural differences in the databases'+CHAR(10)+CHAR(10)
IF @OnlyStructure = 1
BEGIN
PRINT 'The option ''Only compare structures'' was specified. End of work.'
RETURN
END
EXEC ('declare @Name sysname select @Name=d1o.name
from '+@db1+'.dbo.sysobjects d1o, '+@db2+'.dbo.sysobjects d2o
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)')
IF @@ROWCOUNT = 0
BEGIN
PRINT 'There are no tables with the same name and structure in the databases to compare. End of work.'
RETURN
END
END
Automatically Reindex All Tables in a SQL Server Database
This script will automatically reindex all indexes the tables in a
selected database. When DBCC DBREINDEX is used to rebuild indexes,
bear in mind that as the indexes on a specific table are being rebuilt,
that the table becomes unavailable for use by users.
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,'',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,'',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Syntax:
DBCC DBREINDEX
(
table_name
[ , index_name [ , fillfactor ] ]
)
[ WITH NO_INFOMSGS ]
Update Statistics Using T-Sql
This script will update the statistics on all databases on a SQL Server instance (except for master, model and tempdb).
Declare
@dbname varchar(1000),
@parentname varchar(255),
@SQLSTR varchar (1000),
@ctrl char (2),
@command varchar(1000)
Set @ctrl = char (13) + char (10)
Declare DBCUR Cursor Fast_Forward Read_Only For
Select [name]
From sysdatabases Where name not in
(
'master',
'model',
'tempdb'
)
Order By name
Open DBCUR
Fetch Next From DBCUR Into @dbname
While @@Fetch_Status = 0
Begin
Select @command =
'
use ['+@dbname+']
Exec sp_MSForEachtable ''update statistics ? with fullscan''
'
Exec (@command)
Fetch Next From DBCUR Into @dbname
End
Close DBCUR
Deallocate DBCUR
Declare
@dbname varchar(1000),
@parentname varchar(255),
@SQLSTR varchar (1000),
@ctrl char (2),
@command varchar(1000)
Set @ctrl = char (13) + char (10)
Declare DBCUR Cursor Fast_Forward Read_Only For
Select [name]
From sysdatabases Where name not in
(
'master',
'model',
'tempdb'
)
Order By name
Open DBCUR
Fetch Next From DBCUR Into @dbname
While @@Fetch_Status = 0
Begin
Select @command =
'
use ['+@dbname+']
Exec sp_MSForEachtable ''update statistics ? with fullscan''
'
Exec (@command)
Fetch Next From DBCUR Into @dbname
End
Close DBCUR
Deallocate DBCUR
How to encrypt a stored procedure
Inorder to encrypt the text of stored procedures containing sensitive information, Sql Server provides WITH ENCRYPTION to encrypt the Stored Procedure.
Create Procedure [dbo].[spAccountInformation]
With Encryption As
Begin
Set Nocount On
Select AccID,FirstName,LastName,Email
From AccountMaster
Order By Email
End
Once the stored procedure has been created WITH ENCRYPTION, attempts to view the stored procedure returns a message specifying that the text is encrypted:
Exec sp_helptext spAccountInformation
The text for object 'spAccountInformation' is encrypted.
One note of caution. Save the original text or keep int it VSS of the stored procedure before encrypting it, as there is no straightforward way to decode the encrypted text. One hack is to attach a debugger to the server process and retrieve the decrypted procedure from memory at runtime.
Create Procedure [dbo].[spAccountInformation]
With Encryption As
Begin
Set Nocount On
Select AccID,FirstName,LastName,Email
From AccountMaster
Order By Email
End
Once the stored procedure has been created WITH ENCRYPTION, attempts to view the stored procedure returns a message specifying that the text is encrypted:
Exec sp_helptext spAccountInformation
The text for object 'spAccountInformation' is encrypted.
One note of caution. Save the original text or keep int it VSS of the stored procedure before encrypting it, as there is no straightforward way to decode the encrypted text. One hack is to attach a debugger to the server process and retrieve the decrypted procedure from memory at runtime.
Identify Table Fragmentation with DBCC Command
Tests the degree of fragmentation of indexes caused by page
splitting using the DBCC SHOWCONTIG command. As DBCC SHOWCONTIG requires
the ID of both the index table and index you can simply use this script
which accepts the table and index names. DBCC SHOWCONTIG outputs
several key measurements, the most important of which is the Scan
Density. Scan Density should be as close to 100% as possible. A scan
density of below 75% may necessitate a reindexing of all the tables in
the database.
--Script to identify table fragmentation --Declare variables DECLARE@ID int, @IndexID int, @IndexName varchar(128) --Set the table and index to be examined SELECT @IndexName = 'index_name' --enter name of index SET @ID = OBJECT_ID('table_name') --enter name of table --Get the Index Values SELECT @IndexID = IndID FROM sysindexes WHERE id = @ID AND name = @IndexName --Display the fragmentation DBCC SHOWCONTIG (@id, @IndexID)Monday, December 19, 2011
Calling Web Service from T-SQL
This
script calculates calls a web service using T-SQL. To do this OLE Automation
was used. The ServerXMLHTTP.6.0 object is used to accomplish this.
See http://support.microsoft.com/kb/290761 for more information
about this object.
Old Method :
Old Method :
DECLARE @URI varchar(8000),
@output_XML varchar(8000),
@result int,
@object int
set @URI = 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT'
EXEC @result = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @object OUTPUT
IF @result <> 0
BEGIN
RAISERROR('sp_OACreate on MSXML2.XMLHttp.6.0 failed', 16,1)
RETURN
END
EXEC @result = sp_OAMethod @object, 'open', NULL, 'GET', @URI, false
IF @result <>0
BEGIN
RAISERROR('sp_OAMethod Open failed', 16,1)
RETURN
END
EXEC @result = sp_OAMethod @object, SEND, NULL, ''
IF @result <>0
BEGIN
RAISERROR('sp_OAMethod SEND failed', 16,1)
RETURN
END
@output_XML varchar(8000),
@result int,
@object int
set @URI = 'http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT'
EXEC @result = sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @object OUTPUT
IF @result <> 0
BEGIN
RAISERROR('sp_OACreate on MSXML2.XMLHttp.6.0 failed', 16,1)
RETURN
END
EXEC @result = sp_OAMethod @object, 'open', NULL, 'GET', @URI, false
IF @result <>0
BEGIN
RAISERROR('sp_OAMethod Open failed', 16,1)
RETURN
END
EXEC @result = sp_OAMethod @object, SEND, NULL, ''
IF @result <>0
BEGIN
RAISERROR('sp_OAMethod SEND failed', 16,1)
RETURN
END
EXEC @result = sp_OAGetProperty @object,
'responseText', @output_XML OUTPUT
IF @result <>0
BEGIN
RAISERROR('sp_OAGetProperty responseText failed', 16,1)
RETURN
END
SELECT @output_XML StockQouteXML
IF @result <>0
BEGIN
RAISERROR('sp_OAGetProperty responseText failed', 16,1)
RETURN
END
SELECT @output_XML StockQouteXML
EXEC @result = sp_OADestroy @object
IF @result <>0
BEGIN
RAISERROR('sp_OAGetProperty responseText failed', 16,1)
RETURN
END
IF @result <>0
BEGIN
RAISERROR('sp_OAGetProperty responseText failed', 16,1)
RETURN
END
Subscribe to:
Posts (Atom)