Sql server has a date function called DATENAME to get the name of the day or day of week
Oracle day name or day of week function
Mysql has a similar function called dayname() which is equivalent to datename in Microsoft SQL server
Any stored procedure or view or function can be encrypted when stored in the sql server , so a user can not see the code inside even if the user has SYSADMIN role and it is also not recommended to use this encryption option to hide the code because once you use the encryption option Microsoft sql server does not provide the option or the feature to reverse the setting or decrypt the object , so when you use the encryption option , save your code in a separate instance
TITLE: Microsoft SQL Server Management Studio
——————————
Script failed for StoredProcedure ‘dbo.test_encrp’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+StoredProcedure&LinkId=20476
——————————
ADDITIONAL INFORMATION:
Property TextHeader is not available for StoredProcedure ‘[dbo].[test_encrp]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=TextHeader&LinkId=20476
First we will see how it looks in the sql_modules catalog views, in the process we will also list all the encrypted
stored procedures in the databases
to find all the encrypted objects in the sql server database
select o.name,s.definition,o.type_desc,
case
when definition is null then ‘yes’
else ‘no’
end as ‘is_encrypted’
from sys.sql_modules s inner join
sys.objects o on s.object_id = o.object_id
where type in (‘p’,’tr’,’FN’,’tf’,’v’)
now we got know how it is encrypted , and where to find the encrypted objects now we will look in to how to decrypt them
There are two ways you can do
first we will look at the script method
first you need to have SYSADMIN role assigned to you , then you have to setup a DAC connection to the sql server instance that you
want to decrypt the objects
here is the code to decrypt
/****** Object: StoredProcedure [dbo].[sp__procedure] Script Date: 03/06/2012 19:56:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp__procedure]
(@procedure sysname = NULL)
AS
SET NOCOUNT ON
DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@intEncrypted
tinyint,@procNameLength int
select @maxColID = max(subobjid)
–,@intEncrypted = encrypted
FROM
sys.sysobjvalues WHERE objid = object_id(@procedure)
–GROUP BY encrypted
–select @maxColID as ‘Rows in sys.sysobjvalues’
select @procNameLength = datalength(@procedure) + 29
DECLARE @real_01 nvarchar(max)
DECLARE @fake_01 nvarchar(max)
DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)
select @real_decrypt_01a = ”
— extract the encrypted imageval rows from sys.sysobjvalues
SET @real_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 and subobjid = 1 )
— create this table for later use
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX) )
— We’ll begin the transaction and roll it back later
BEGIN TRAN
— alter the original procedure, replacing with dashes
SET @fake_01=’ALTER PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(‘-‘, 40003 – @procNameLength)
EXECUTE (@fake_01)
— extract the encrypted fake imageval rows from sys.sysobjvalues
SET @fake_encrypt_01=(SELECT imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 and subobjid = 1)
SET @fake_01=’CREATE PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(‘-‘, 40003 – @procNameLength)
–start counter
SET @intProcSpace=1
–fill temporary variable with with a filler character
SET @real_decrypt_01 = replicate(N’A’, (datalength(@real_01) /2 ))
–loop through each of the variables sets of variables, building the real variable
–one byte at a time.
SET @intProcSpace=1
— Go through each @real_xx variable and decrypt it, as necessary
WHILE @intProcSpace<=(datalength(@real_01)/2)
BEGIN
–xor real & fake & fake encrypted
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END
— Load the variables into #output for handling by sp_helptext logic
insert #output (real_decrypt) select @real_decrypt_01
— select real_decrypt AS ‘#output chek’ from #output — Testing
— ————————————-
— Beginning of extract from sp_helptext
— ————————————-
declare @dbname sysname
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int –lengths of line feed carriage return
,@DefinedLength int
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0
–Keeps track of blank spaces at end of lines. Note Len function ignores trailing blank spaces
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)
— use #output instead of sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT real_decrypt from #output
ORDER BY ident
FOR READ ONLY
— Else get the text.
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT FROM ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
–Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
@BasePos)
–If carriage return found
IF @CurrentPos != 0
BEGIN
–If new value for @Lines length will be > then the
–set length then insert current contents of @line
–and proceed.
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +
@BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N”)
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
–else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
–If new value for @Lines length will be > then the
–defined length
—
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength –
(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N”)
if LEN(@Line) < @DefinedLength and charindex(‘ ‘,
@SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ‘ ‘, @BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT FROM ms_crs_syscom into @SyscomText
END
IF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )
select Text from #CommentText order by LineId
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText
— ————————————-
— End of extract from sp_helptext
— ————————————-
— Drop the procedure that was setup with dashes and rebuild it with the good stuff
— Version 1.1 mod; makes rebuilding hte proc unnecessary
ROLLBACK TRAN
DROP TABLE #output
GO
the second way is to use the third party tools , there many third party tools that are available to download
one of the best is the Optillect sql decrypter which is a free ware, good thing about it is it does not need SYSADMIN role as it does not
use DAC , rather it uses DBCC ind, and DBCC PAGE for the required information for the SYS.OBJVALUES table
http://download.cnet.com/Optillect-SQL-Decryptor/3640-10254_4-75300271-1.html
download and install it
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘CHECKIDENT’ when IDENTITY_INSERT is set to OFF.
USE [SQLJUNKIESHARE]
GO
CREATE TABLE [dbo].[CHECKIDENT](
[ID] [int] IDENTITY(1,1) NOT NULL,
[date] [date] NULL,
[descp] [varchar](10) NULL
) ON [PRIMARY]
GO
-- How to insert explicit values in identity columns with out altering table
INSERT INTO DBO.CHECKIDENT(ID,descp)
VALUES(1,'CHECK')
-- WHEN SET TO ON WE CAN INSERT EXPLICIT VALUES IN TO THE TABLE
SET IDENTITY_INSERT DBO.CHECKIDENT ON
INSERT INTO DBO.CHECKIDENT(ID,descp)
VALUES(1,'CHECK')
INSERT INTO DBO.CHECKIDENT(ID,descp)
VALUES(1,'CHECK')
SELECT * FROM DBO.CHECKIDENT
SET IDENTITY_INSERT DBO.CHECKIDENT OFF
SQL Server provides the following set operators. Set operators combine results from two or more queries into a single result set
1) Intersect and Except
2) Union
Today in this post we will learn Intersect and Except set operators
Returns distinct values by comparing the results of two queries.
EXCEPT returns any distinct values from the left query that are not also found on the right query.
INTERSECT returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.
The basic rules for combining the result sets of two queries that use EXCEPT or INTERSECT are the following:
Lets take a simple example and you will better understand the set operators
create table #newproducts(ProductID int, name varchar(20), size varchar(20),price varchar(20))
insert into #newproducts(ProductID,name,size,price)
values(1,’productx’,’small’,20),
(2,’producty’,’small’,40),
(3,’productz’,’large’,50),
(1,’productxy’,’small’,60)
create table #oldproducts(ProductID int, name varchar(20), size varchar(20),price varchar(20))
insert into #oldproducts(ProductID,name,size,price)
values(4,’productq’,’small’,20),
(5,’productk’,’small’,40),
(6,’productz’,’large’,50),
(1,’productxy’,’small’,60),
(1,’productzy’,’small’,80)
select * from #newproducts
select * from #oldproducts
;with oldonly as
(
select * from #oldproducts
except
select * from #newproducts
),
newonly as (select * from #newproducts
except
select * from #oldproducts)
select o.ProductID,o.name as oldname, n.name as newname
from oldonly o inner join newonly n on o.ProductID = n.ProductID
where o.name <> n.name