Look what I got in the post… Free stuff from Microsoft. Well true you need to pass a few exams to get them, but how cool is the swag. I did wonder what UPS had sent me and had completely forgot this was coming. Good luck to all those on the road to getting them.
SQL Server Page Types
There are lots of different page types and Paul Randall list them in his article. However as I tend to have a rather poor memory for remembering them first off here are the ones that can be anywhere within the database.
m_type | |
1 | DATA_PAGE |
2 | INDEX_PAGE |
3 | TEXT_MIX_PAGE |
4 | TEXT_TREE_PAGE |
7 | SORT_PAGE |
10 | IAM_PAGE |
Now the other ones should be in predictable locations assuming I have the script coded correctly. I have added in a few example page locations, as well as looking in to the suspect pages table as in a restoration scenario only certain pages can be restored using page restore.
SELECT *, CASE WHEN [page_id] = 0 THEN 'File Header Page m_type 15' WHEN [page_id] = 1 OR [page_id] % 8088 = 0 THEN 'PFS m_type 11' WHEN [page_id] = 2 OR [page_id] % 511232 = 0 THEN 'GAM m_type 8' WHEN [page_id] = 3 OR ([page_id] - 1) % 511232 = 0 THEN 'SGAM m_type 9' WHEN [page_id] = 6 OR ([page_id] - 6) % 511232 = 0 THEN 'DCM m_type 16' WHEN [page_id] = 7 OR ([page_id] - 7) % 511232 = 0 THEN 'BCM m_type 17' WHEN [page_id] = 9 AND [file_id] = 1 THEN 'Boot Page m_type 13' --DBCC DBINFO WITH TABLERESULTS WHEN [page_id] = 10 AND DB_ID() = 1 THEN 'config page -> sp_configure settings only present in master m_type 14' ELSE 'Other' END [Description], 'DBCC PAGE('''+DB_NAME()+''','+LTRIM(STR(x.[file_id]))+','+LTRIM(STR(x.[page_id]))+',3) WITH TABLERESULTS' [Page] FROM ( SELECT 0 [page_id],1 [file_id] UNION ALL -- A File Header Page SELECT 1 [page_id],1 [file_id] UNION ALL -- A PFS Page --SELECT 8088 [page_id],1 [file_id] UNION ALL -- A PFS Page --SELECT 16176 [page_id],1 [file_id] UNION ALL -- A PFS Page SELECT 2 [page_id],1 [file_id] UNION ALL -- A GAM Page --SELECT 511232 [page_id],1 [file_id] UNION ALL -- A GAM Page SELECT 3 [page_id],1 [file_id] UNION ALL -- A SGAM page --SELECT 511233 [page_id],1 [file_id] UNION ALL -- A SGAM page SELECT 6 [page_id],1 [file_id] UNION ALL -- A DCM page --SELECT 511238 [page_id],1 [file_id] UNION ALL -- A DCM page SELECT 7 [page_id],1 [file_id] UNION ALL -- A BCM page --SELECT 511239 [page_id],1 [file_id] UNION ALL -- A BCM page SELECT 9 [page_id],1 [file_id] UNION ALL -- The Boot Page SELECT [page_id],[file_id] FROM msdb.dbo.suspect_pages ) x
Acknowledgement
This m_type values were gathered from Paul Randall’s blog, and the script works along the same lines as Robert L Davis blog post on tempdb contention.
http://www.sqlsoldier.com/wp/sqlserver/breakingdowntempdbcontentionpart2
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/
SQL Server Row Internals
Row Storage
A Row is stored on a Page which is 8192 Bytes, and this is broken down in two key areas when considering physical storage. Firstly on all pages there is a 96 Byte Header. In addition to this we also need to store the Row structure as well as its location in the remainder of the Page.
The location of a Row is stored at the end of the Page working backwards using 2 Bytes per Row. These 2 Byte pairs are generally called the Slot Array. I have seen a few articles/explanations of the Slot Array listing this as 36 Bytes which is incorrect, unless of course you are storing 18 Rows.
Now as the Page Header is always 96 Bytes, if you were to create a table and insert a Row into it; then the earliest point that it could be placed is immediately after the Page Header.
This location immediately after the Page Header would mean that the final 2 Bytes of the Page (Byte 8190 and Byte 8191) would be 60 00 which when reversed becomes 0x60 or 96 as an integer.
If another Row is added then Byte 8188 and Byte 8189 would be used to store the location of this second Row. The order of these 2 Byte pairs within the Slot Array is determined by how they are indexed, not necessarily how they are stored on the Page. That is to say a Row stored towards the front of the Page may or may not appear towards the beginning of the Slot Array which grows from the end of the Page towards the start.
Here is a representation of how a row is stored internally within SQL server using the standard structure.
Desc | Bytes |
Page Header | 96 |
Status Byte A | 1 |
Status Byte B | 1 |
Null Bitmap Offset | 2 |
Fixed Length Columns | X |
Column Count | 2 |
Null Bitmap(At least 1 Byte) | 1(+1 per 8 Columns) |
Variable Length Column Count | 2 |
Variable Length Offset Array | 2 per column* |
Uniqueifier | 4 (if needed) |
Variable Length Columns | X |
Row Version Contains timestamp + pointer to original record | 14 bytes |
Slot Array | 2 bytes per Record/Row/Slot |
Now you will see that the fixed length columns are stored towards the start of the structure and then the variable length columns. *I have highlighted that the variable length column array is 2 Bytes per column, however if a column is null then the entry in the Slot Array is not needed unless it has a non null column to its right.
[Location A][Location B][Location C] AAA,BBB,CCC
[Location A][Location B][Location C] AAA,NULL,CCC
[Location A][Location B][Location C] AAA,BBB,NULL
In the first 2 examples all 3 location pointers are needed, however the 3rd example does not require the 3rd location pointer. This raises the interesting point that column order does matter, and that you should keep the variable length columns that have a high probability to remain NULL after variable lengths columns that are not.
Smallest Row Size
The smallest row size using the above mentioned format would be for a CHAR(1)
+1 Status Byte A
+1 Status Byte B
+2 Null Bitmap Offset
+1 Char(1)
+2 Column Count
+1 Null Bitmap (This is always stored regardless)
This would work out as 8 Bytes for the Row plus 2 Bytes per the Row location in the Slot Array. Now it is worth noting however that this is not the smallest Row size as internally this would be padded by 1 additional Byte. If you store a CHAR(2) then no padding will take place. The reason this happens is because of an optimization which is best explained as follows:
The reason that the data record is 9 bytes is that for heaps we need to guarantee that the RID that is used to uniquely identify a row in a heap is never changed. To do this, SQL Server needs to be able to store at least a forwarding pointer on a page. A forwarding pointer is 9 bytes (1 byte header, and 8 byte RID of the actual record.
For B-Trees, the 9 byte limit is used as well. The reason here is that SQL Server quickly likes to convert a B-Tree to a heap without having to touch all the individual records. This is only possible if we can guarantee that the records are at least 9 bytes long.
Marcel van der Holst – MSFT
So although the Row is 8 Bytes it is stored using 9 Bytes. The smallest optimally stored Row therefore is a fixed data type of 2 Bytes.
Most Rows per Page
On that note then if we say that we have a 9 Byte Row Structure + 2 Bytes for the Slot Array to store in the 8096 Bytes (8192-96 Page Header) we can work out the most Rows per Page as follows:
8096/11 = 736 Rows
Acknowledgement
This post builds on information gathered from research, but the factual knowledge was gathered from Paul Randall blog posts.
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/
MCM 2008 Success
I got an email from Microsoft! The lab I took on the 11th April. (Yesterday). I passed!!!
So…. a long time ago in a place….no wait…
I passed the prerequisite and then spent many days with my books and study material to pass the knowledge exam which as I said before was hard. The lab exam itself is hard. No wait it is very hard. The problem I found about the process was not really knowing what to expect with the content. Failing the lab on my first attempt gave me some pointers and areas to improve. I guess every failure should really teach you something. If you keep doing the same thing and expecting different results… you are very much like my cat.
Anyway…I took the lab yesterday on the 11th (From my home) and on the 12th I have the result. I have passed and am very proud to count myself among fellow MCM holders. I can relax now and absorb 2012 knowledge at SQLBits.
SQL Server Partitioning Data Movement
Introduction
This is a continuation to the partitioning overview post to help explain how data movement is performed using Alter Partition with Merge and Split Commands.
Getting Started
In order to move data in or out of a partitioned object the part of the object that needs to be moved must reside on the same file group. In the first post all the partitions were placed on the primary file group. Now if the middle partition P2 was removed using the MERGE syntax this would result in P3 renumbering itself to P2. So to make things easier to follow the next example will create file groups called One,Two,Three and Four which will correspond to the initial partition locations.
The code below is for a RANGE RIGHT partition, but included is the code for a RANGE LEFT.
CREATE DATABASE [MyTest] GO USE [master] GO ALTER DATABASE [MyTest] ADD FILEGROUP [One] ALTER DATABASE [MyTest] ADD FILEGROUP [Two] ALTER DATABASE [MyTest] ADD FILEGROUP [Three] ALTER DATABASE [MyTest] ADD FILEGROUP [Four] GO ALTER DATABASE [MyTest] ADD FILE (NAME = N'One_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\One_File01.ndf') TO FILEGROUP [One] GO ALTER DATABASE [MyTest] ADD FILE (NAME = N'Two_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Two_File01.ndf') TO FILEGROUP [Two] GO ALTER DATABASE [MyTest] ADD FILE (NAME = N'Three_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Three_File01.ndf') TO FILEGROUP [Three] GO ALTER DATABASE [MyTest] ADD FILE (NAME = N'Four_File01',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Four_File01.ndf') TO FILEGROUP [Four] GO USE [MyTest] CREATE PARTITION FUNCTION pfR(INT) AS RANGE RIGHT FOR VALUES (100,200,300); CREATE PARTITION SCHEME psR AS PARTITION pfR TO ([One],[Two],[Three],[Four]); CREATE TABLE R ([ID] INT) ON psR([ID]); INSERT INTO R VALUES (NULL) INSERT INTO R VALUES (-1) INSERT INTO R VALUES (0) INSERT INTO R VALUES (1) INSERT INTO R VALUES (100) INSERT INTO R VALUES (101) INSERT INTO R VALUES (200) INSERT INTO R VALUES (201) INSERT INTO R VALUES (300) INSERT INTO R VALUES (301) INSERT INTO R VALUES (1001) /* CREATE PARTITION FUNCTION pfL(INT) AS RANGE LEFT FOR VALUES (100,200,300); CREATE PARTITION SCHEME psL AS PARTITION pfL TO ([One],[Two],[Three],[Four]); CREATE TABLE L ([ID] INT) ON psL([ID]); INSERT INTO L VALUES (NULL) INSERT INTO L VALUES (-1) INSERT INTO L VALUES (0) INSERT INTO L VALUES (1) INSERT INTO L VALUES (100) INSERT INTO L VALUES (101) INSERT INTO L VALUES (200) INSERT INTO L VALUES (201) INSERT INTO L VALUES (300) INSERT INTO L VALUES (301) INSERT INTO L VALUES (1001) --DROP TABLE L --DROP PARTITION SCHEME psL --DROP PARTITION FUNCTION pfL --DROP TABLE R --DROP PARTITION SCHEME psR --DROP PARTITION FUNCTION pfR */
You can check where each row is using the Detailed Table and Index Breakdown script
Merge
Using the example below of a RANGE RIGHT partition what would happen if the secondary boundary was removed.
In order to remove the secondary boundary this command is needed:
ALTER PARTITION FUNCTION pfR() MERGE RANGE(200)
The resultant change is that the partition to the right of the boundary shown below is removed and the data is moved to partition 2. The example has rows in both partitions, but this would also be true if both P2 and P3 had been empty, except no data would have been moved.
But what if instead the values 100 and 101 had not been inserted and P2 was empty?
I need to add a note here to say that the following is true from SQL 2012 with regards to empty partitions, as was highlighted this is not true for versions prior. For previous versions see the following post: http://www.brentozar.com/archive/2013/01/best-practices-table-partitioning-merging-boundary-points/
In this case data is not moved from P3 but instead P2 is removed.
If the merge was on a RANGE LEFT example then it will remove the left partition. The easiest way to think about this is that the partition that satisfies the boundary condition will be removed, unless there are no rows in the other partition that does not satisfy the boundary condition. Hopefully that is clear if you imagine the two sides of your boundary.
If nulls are allowed on the partition column then they will be stored in P1, which is important when considering range left or right. A partition merge on a range right that has nulls in P1 will remove P2, where as a range left will remove P1.
Split
If you want to add partitions you must first specify the next filegroup to use. In order to put back the partition removed from the split above the following code is needed:
Right-
ALTER PARTITION SCHEME psR NEXT USED [Three] GO ALTER PARTITION FUNCTION pfR() SPLIT RANGE(200)
Left-
ALTER PARTITION SCHEME psL NEXT USED [Two] GO ALTER PARTITION FUNCTION pfL() SPLIT RANGE(200)
Switch
Switching is perhaps the most useful of the movement commands in that you can move data incredibly easily.
If the contents of P2 needed to be moved out we would first need to create a destination table before switching P2. The destination table does not need to be partitioned if we are moving only one source partition. In some cases it maybe easier to create an exact copy of your source structure as then you can move partitions specifying each partition number as needed.
CREATE TABLE R2 ([ID] INT) ON [Two]; GO ALTER TABLE [dbo].[R] SWITCH PARTITION 2 TO R2
The two rows from P2 of the source table are now present in the destination table and P2 of source is showing as zero rows.
Now to put the rows back…
ALTER TABLE [dbo].[R2] SWITCH TO [dbo].[R] PARTITION 2
ALTER TABLE SWITCH statement failed. Check constraints of source table ‘dbo.R2’ allow values that are not allowed by range defined by partition 2 on target table ‘dbo.R’.
In order for this to work we need to confirm that the data in the R2 table will fit in to P2, which means a constraint. Now P2 should hold values that are greater than or equal to 100, but less than 200. (Nulls are held in P1)
ALTER TABLE [dbo].[R2] ADD CONSTRAINT [PleaseBelieveMe] CHECK ([ID]>=N'100' AND [ID]<N'200' AND [ID] IS NOT NULL)
The switch will now work correctly and the rows are returned. This process becomes slightly more complicated with indexes but the theory is the same and is explained well in Special Guidelines for Partitioned Indexes.
The only issue that I have had with SWITCH is when conflict detection is enabled on a peer to peer replication, which is logged here on connect.
SQL Server Partitioning Overview
Introduction
This is a guide to help explain partitioning setup; which although introduced way back in SQL 2005 is still an area which throws up a lot of questions.
Requirements
First off you will need Enterprise SQL Server in your production environment, or alternatively you can explore this feature in SQL Developer edition.
Getting Started
In order to partition there are two objects that you will first need to create these are a PARTITION FUNCTION and a PARTITION SCHEME.
Function
The partition function is the first building block that you need and requires that you specify boundaries.
CREATE PARTITION FUNCTION pfL(INT) AS RANGE LEFT FOR VALUES (100,200,300);
The syntax is simple and shows that in the above case the function expects an integer and will create boundaries based on a RANGE LEFT strategy. The example shows three boundaries which will mean that there are four partitions. Number of boundaries + 1.
RANGE LEFT means that the boundary points are <= less than or equal. In the example below the first boundary is 100 which means all values less than or equal to 100 will go in to the first partition, those above 100 and less than the next boundary will fit in to partition two (P2) and so on.
The below image shows this and with example values sorted.
Now obviously having shown RANGE LEFT, here is a RANGE RIGHT example. Note that now the boundaries points are => greater than or equal to. So the value 100 for example is now in the second partition.
Scheme
Before we can apply the partition, a partition scheme needs to be deployed which will identify the file groups to use.
CREATE PARTITION SCHEME psL AS PARTITION pfL TO ([PRIMARY],[PRIMARY],[PRIMARY],[PRIMARY]);
The table below specifies that it will use the partition scheme and will be partitioned on the ID column and to make this example simple all the data is stored on the Primary file group.
CREATE TABLE L ([ID] INT) ON psL([ID]) INSERT INTO L VALUES (NULL) INSERT INTO L VALUES (-1) INSERT INTO L VALUES (0) INSERT INTO L VALUES (1) INSERT INTO L VALUES (100) INSERT INTO L VALUES (101) INSERT INTO L VALUES (200) INSERT INTO L VALUES (201) INSERT INTO L VALUES (300) INSERT INTO L VALUES (301) INSERT INTO L VALUES (1001)
You can check where each row is using the Detailed Table and Index Breakdown script
Find Uptime / Last Boot Up Time Commands
There are numerous ways to find how long a machine has been powered on for, and below I will list a few of them:
CMD
systeminfo | find "System Boot Time"
net statistics server | find "Statistics since"
net statistics workstation | find "Statistics since"
Powershell
$LastBootUpTime = [System.Management.ManagementDateTimeConverter]::ToDateTime($(gwmi Win32_OperatingSystem).LastBootUpTime) $d = New-Timespan $LastBootUpTime (Get-Date) "{0} days {1} hours {2} minutes" -f $d.days,$d.hours,$d.minutes
SQL Events 2013
2013 is shaping up to be a good year for me getting to events, so far penciled in are SQLSaturday in Exeter and SQLBits XI in Nottingham. Hope to see lots of you there… In fact you can even register for SQLSaturday right now. So go do that.
SQLSaturday #194 8th and 9th March 2013 Exeter
http://www.sqlsaturday.com/194/eventhome.aspx
SQLBits XI 2nd to 4th May 2013 Nottingham
http://sqlbits.com/
SQL Object Decryption
Background
The ability to “decrypt” an object in SQL Server be it stored procedure, view, function or trigger is something that I have run into a few times. In this post I will show you how you can “decrypt” any sql object using a simple script and highlight the things to be aware of.
Firstly my annoying use of “air quotes” is something that you would have noticed and that is because when you use “WITH ENCRYPTION” you are actually only obfuscating the object definition. The process to “decrypt” is very straight forward and requires only one pre-requiste the Dedicated Admin Connection (DAC) which I will explain later, but first I want to talk about how this solution came to be needed.
Solution History
When I first needed to decrypt objects for SQL Server 2000 I had always used a solution posted by Joseph Gama which is based on work by shoeboy. Here is a link to that SQL 2000 solution. However when I went to use this on SQL 2005 it no longer worked…
The reason why the SQL 2000 solution no longer functioned was because the encrypted object values were no longer present in the ctext column of the syscomments table. They had been moved to the imageval column of sys.sysobjvalues, which can only be accessed when using the Dedicated Admin Connection (DAC).
The Dedicated Admin Connection (DAC) is a single connection for the administrator of SQL Server to access the instance. This is important because it gives access to objects that are otherwise hidden.
So that is the basic background to this updated solution. I needed to decrypt objects in later version of SQL Server and so I rewrote a solution using the same decryption methods of the original, but using the new location for the encrypted objects defintions. I also wanted to avoid dropping the encrypted object(s) and so changed this to an alter with rollback, as well as adding in the new object types.
I had kept my rewrite to myself for sometime until it came up on the forum in 2010 http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e7056ca8-94cd-4d36-a676-04c64bf96330/ .
I have since come to realize that perhaps this is not as visible as it could be and so I have given it a new home here where hopefully I can get feedback and address any bugs (undocumented features) without resurrecting that post.
How it works
The decryption method works as it has done in all similar versions of this code.
Encrypted Object ^ Template Object ^ Dummy Encrypted Object
For those interested in exactly what is going on please see the XOR_cipher wikipedia entry.
Getting it to work
In order to decrypt objects as I said you will need to access an object that is not available via a standard connection so for this you will need to enable Dedicated Admin Connection (DAC) if not already enabled.
EXEC sp_configure 'remote admin connections', 1 GO RECONFIGURE GO
Additionally if you are using SQL Express you will need to enable trace flag 7806.
Then with this done open SSMS, choose File, New, Database Engine Query and set your servername to be admin:ServerName
The last step is to change the code lines to reference the object you want the underlying text for and then check the messages tab.
Below is the code:
--http://jongurgul.com/blog/sql-object-decryption/ DECLARE @EncObj VARBINARY(MAX),@DummyEncObj VARBINARY(MAX),@ObjectNameStmTemplate NVARCHAR(MAX) SET NOCOUNT ON /* --You must be using a DAC. SELECT * FROM sys.dm_exec_connections ec JOIN sys.endpoints e on (ec.[endpoint_id]=e.[endpoint_id]) WHERE e.[name]='Dedicated Admin Connection' AND ec.[session_id] = @@SPID */ USE [master] --change to where your encrypted object resides DECLARE @object_id INT,@name sysname SELECT @object_id = [object_id],@name = [name] FROM sys.all_objects WHERE name = N'jjj' --<=Either put your object name here or make sure @object_id is set, and that the object it relates to is encrypted. SELECT TOP 1 @ObjectNameStmTemplate = [ObjectStmTemplate] ,@EncObj = [imageval] FROM ( SELECT SPACE(1)+ ( CASE WHEN [type] = 'P' THEN N'PROCEDURE' WHEN [type] = 'V' THEN 'VIEW' WHEN [type] IN ('FN','TF','IF') THEN N'FUNCTION' WHEN [type] IN ('TR') THEN N'TRIGGER' ELSE [type] END ) +SPACE(1)+QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME(ao.[name])+SPACE(1)+ ( CASE WHEN [type] = 'P' THEN N'WITH ENCRYPTION AS' WHEN [type] = 'V' THEN N'WITH ENCRYPTION AS SELECT 123 ABC' WHEN [type] IN ('FN') THEN N'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END' WHEN [type] IN ('TF') THEN N'() RETURNS @t TABLE(i INT) WITH ENCRYPTION AS BEGIN RETURN END' WHEN [type] IN ('IF') THEN N'() RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT 1 N' WHEN [type] IN ('TR') THEN N' ON ' + OBJECT_NAME(ao.[parent_object_id]) + ' WITH ENCRYPTION FOR DELETE AS SELECT 1 N' ELSE [type] END ) +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT ,sov.[imageval] FROM sys.all_objects ao INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND ao.[Object_id] = sov.[objid] WHERE [type] NOT IN ('S','U','PK','F','D','SQ','IT','X','PC','FS','AF','TR') AND ao.[object_id] = @object_id UNION ALL --Server Triggers SELECT SPACE(1)+'TRIGGER'+SPACE(1)+QUOTENAME(st.[name])+SPACE(1)+N'ON ALL SERVER WITH ENCRYPTION FOR DDL_LOGIN_EVENTS AS SELECT 1' +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT ,sov.[imageval] FROM sys.server_triggers st INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND st.[object_id] = sov.[objid] WHERE st.[object_id] = @object_id --Database Triggers UNION ALL SELECT SPACE(1)+'TRIGGER'+SPACE(1)+QUOTENAME(dt.[name])+SPACE(1)+N'ON DATABASE WITH ENCRYPTION FOR CREATE_TABLE AS SELECT 1' +REPLICATE(CAST(N'-' AS NVARCHAR(MAX)),DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT ,sov.[imageval] FROM sys.triggers dt INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1 AND dt.[object_id] = sov.[objid] AND dt.[parent_class_desc] = 'DATABASE' WHERE dt.[object_id] = @object_id ) x([ObjectStmTemplate],[imageval]) --Alter the existing object, then revert so that we have the dummy object encrypted value BEGIN TRANSACTION DECLARE @sql NVARCHAR(MAX) SET @sql = N'ALTER'+@ObjectNameStmTemplate EXEC sp_executesql @sql SELECT @DummyEncObj = sov.[imageval] FROM sys.all_objects ao INNER JOIN sys.sysobjvalues sov ON sov.[valclass]=1 AND ao.[Object_id]=sov.[objid] WHERE ao.[object_id] = @object_id ROLLBACK TRANSACTION DECLARE @Final NVARCHAR(MAX) SET @Final = N'' DECLARE @Pos INT SET @Pos = 1 WHILE @Pos <= DATALENGTH(@EncObj)/2 BEGIN SET @Final = @Final + NCHAR(UNICODE(SUBSTRING(CAST(@EncObj AS NVARCHAR(MAX)),@Pos,1))^(UNICODE(SUBSTRING(N'CREATE'+@ObjectNameStmTemplate COLLATE DATABASE_DEFAULT,@Pos,1))^UNICODE(SUBSTRING(CAST(@DummyEncObj AS NVARCHAR(MAX)),@Pos,1)))) SET @Pos = @Pos + 1 END --If the object is small then just print, else print in chunks IF DATALENGTH(@Final) <= 8000 BEGIN PRINT '--SMALL--' PRINT @Final END ELSE BEGIN PRINT '--BIG--' DECLARE @c INT SET @c = 0 WHILE @c <= (DATALENGTH(@Final)/8000) BEGIN PRINT SUBSTRING(@Final,1+(@c*4000),4000) SET @c = @c + 1 END END
Get-StringHash and Get-FileHash
Hashing
Here are two of my powershell scripts that provide a quick and easy way to hash either a string or a file using any of the cryptography hash algorithms.
Get-StringHash
#http://jongurgul.com/blog/get-stringhash-get-filehash/ Function Get-StringHash([String] $String,$HashName = "MD5") { $StringBuilder = New-Object System.Text.StringBuilder [System.Security.Cryptography.HashAlgorithm]::Create($HashName).ComputeHash([System.Text.Encoding]::UTF8.GetBytes($String))|%{ [Void]$StringBuilder.Append($_.ToString("x2")) } $StringBuilder.ToString() }
Usage Examples:
Get-StringHash “My String to hash” “MD5”
Get-StringHash “My String to hash” “RIPEMD160”
Get-StringHash “My String to hash” “SHA1”
Get-StringHash “My String to hash” “SHA256”
Get-StringHash “My String to hash” “SHA384”
Get-StringHash “My String to hash” “SHA512”
http://gallery.technet.microsoft.com/scriptcenter/Get-StringHash-aa843f71
Get-FileHash
#http://jongurgul.com/blog/get-stringhash-get-filehash/ Function Get-FileHash([String] $FileName,$HashName = "MD5") { $FileStream = New-Object System.IO.FileStream($FileName,[System.IO.FileMode]::Open) $StringBuilder = New-Object System.Text.StringBuilder [System.Security.Cryptography.HashAlgorithm]::Create($HashName).ComputeHash($FileStream)|%{[Void]$StringBuilder.Append($_.ToString("x2"))} $FileStream.Close() $FileStream.Dispose() $StringBuilder.ToString() }
Usage Examples:
Get-FileHash “C:\MyFile.txt” “MD5”
Get-FileHash “C:\MyFile.txt” “RIPEMD160”
Get-FileHash “C:\MyFile.txt” “SHA1”
Get-FileHash “C:\MyFile.txt” “SHA256”
Get-FileHash “C:\MyFile.txt” “SHA384”
Get-FileHash “C:\MyFile.txt” “SHA512”
http://gallery.technet.microsoft.com/scriptcenter/Get-FileHash-83ab0189