Saturday, August 24, 2013

Data cloaking inside of BMP pictures

Tonight, I decided to create a small program that:
  • encodes any file into a picture
  • decodes this file from the same picture
A schema being better than any long sentence...


How does it work?

So easy... you know a bitmap file is nothing more than a succession of pixels, and a pixel is made of 3 bytes Red Green Blue, right? (let's ignore the file header, the reverse order of rows, etc etc... that's not important here)
The big picture is to hide one byte of the message per pixel. Red is coded on 8 bits, right? Let's use the 2 least significant bits (LSB) to encode 2 bits of this message byte. Green is coded on 8 bits too, so let's use the 3 LSB to encode 3 bits of the message byte. Same thing for Blue, let's use the 3 LSB to encode 3 bits of the message byte. You got it: 2 bits + 3 bits + 3 bits = 1 byte. This is how we will hide each byte from the message in one pixel.
The good news is that we always modified the LSB of the pixel components... when you will look at the modified picture (containing the message), you will not be able to detect the color changes from the original one.

Now, have a closer look at the two interesting parts in this program: encoding of data, decoding of data.

Encoding

The following method inserts a message (a memory Stream, it can be anything) into the original picture. Of course, we have to ensure the picture file is big enough to contain the message.
Note the the first 4 pixels will be used to encode the size of the message. We will need it to decode the message.
The source code is a bit messy, because it contains a homemade BMP writer. But the data cloaking itself is quite straightforward, provided you are familiar with bitmasks.

private void ProcessBmp(string InputFilename, System.IO.MemoryStream Message, string OutputFilename)
{
    using (System.IO.FileStream fs = new System.IO.FileStream(InputFilename, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read, 1024 * 1024))
    {
        using (System.Drawing.Bitmap b = new System.Drawing.Bitmap(fs))
        {
            if (b.Width * b.Height < Message.Length + 4)
                throw new Exception(string.Format("BMP file is too small, it must be at least {0} bytes to encode this message", (Message.Length + 4) * 3));
            using (System.IO.BinaryWriter bw = new System.IO.BinaryWriter(new System.IO.FileStream(OutputFilename, System.IO.FileMode.Create, System.IO.FileAccess.Write)))
            {
                int ComplementToAdd = (4 - ((b.Width * 3) % 4)) % 4;
                byte[] BlackPixel = new byte[3] { 0, 0, 0 };
                bw.Write('B');
                bw.Write('M');

                int x, y;

                bw.Write((uint)(54 + b.Width * b.Height * 3 + b.Height * ComplementToAdd));
                bw.Write((uint)0);
                bw.Write((uint)54);
                bw.Write((uint)40);
                bw.Write((uint)b.Width);
                bw.Write((uint)b.Height);
                bw.Write((uint)24 * 0x10000 + 1);   // planes & bitcount
                bw.Write((uint)0);   // compression
                bw.Write((uint)b.Width * b.Height * 3 + ComplementToAdd * b.Height);    // size of image
                bw.Write((uint)0);   // XPPM
                bw.Write((uint)0);   // YPPM
                bw.Write((uint)0);   // color used

                int Idx = 0;
                for (y = b.Height - 1; y >= 0; y--)
                {
                    for (x = 0; x < b.Width; x++)
                    {
                        Color c = b.GetPixel(x, y);
                        byte[] RGB = new byte[3] { c.B , c.G, c.R };

                        byte ByteToEncode;
                        switch (Idx)
                        { // The 4 first bytes wil be used to encode the size of the message
                            case 0: ByteToEncode = (byte)(Message.Length & 0x000000FF); break;
                            case 1: ByteToEncode = (byte)((Message.Length & 0x0000FF00) >> 8); break;
                            case 2: ByteToEncode = (byte)((Message.Length & 0x00FF0000) >> 16); break;
                            case 3: ByteToEncode = (byte)((Message.Length & 0xFF000000) >> 32); break;
                            default: ByteToEncode = Idx - 4 < Message.Length
                                 ? (byte)Message.ReadByte()
                                 : (byte)0; break;
                        }

                        byte EncodeB = (byte)((ByteToEncode & (32 + 64 + 128)) >> 5);
                                       // Find the biggest 3 bits in the byte to encode
                        byte EncodeG = (byte)((ByteToEncode & (4 + 8 + 16)) >> 2);
                                       // Find the middle 3 bits in the byte to encode
                        byte EncodeR = (byte)(ByteToEncode & (1 + 2));
                                       // Find the smallest 2 bits in the byte to encode
                        RGB[0] = (byte)((RGB[0] & 0xF8) + EncodeB);
                                 // In Blue, 3 bits are used to encode (0xF8 = 1111 1000)
                        RGB[1] = (byte)((RGB[1] & 0xF8) + EncodeG);
                                 // In Green, 3 bits are used to encode (0xF8 = 1111 1000)
                        RGB[2] = (byte)((RGB[2] & 0xFC) + EncodeR);
                                 // In Red, 2 bits are used to encode (0xFC = 1111 1100)

                        bw.Write(RGB, 0, 3);
                        Idx++;
                    }
                    // Rows in bmp must be a multiple of 4, so let's fill with black pixels if necessary
                    if (ComplementToAdd != 0)
                        bw.Write(BlackPixel, 0, ComplementToAdd);
                }
            }
        }
    }
}

Decoding

This one is much easier than the encoding source code. So straightforward that is does not need any comment...

private System.IO.MemoryStream UnprocessBmp(string InputFilename)
{
    System.IO.MemoryStream Result = new System.IO.MemoryStream();
    using (System.IO.FileStream fs = new System.IO.FileStream(InputFilename, System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read, 1024 * 1024))
    {
        using (System.Drawing.Bitmap b = new System.Drawing.Bitmap(fs))
        {
            int x, y, MessageSize = 0, Idx = 0;
            for (y = b.Height - 1; y >= 0; y--)
            {
                for (x = 0; x < b.Width; x++)
                {
                    Color c = b.GetPixel(x, y);
                    byte EncodedB = (byte)(c.B & 0x07);
                                    // Find the last 3 bits of the blue component
                    byte EncodedG = (byte)(c.G & 0x07);
                                    // Find the last 3 bits of the green component
                    byte EncodedR = (byte)(c.R & 0x03);
                                    // Find the last 2 bits of the red component
                    byte EncodedByte = (byte)((EncodedB << 5) + (EncodedG << 2) + EncodedR);
                                    // combine the 3 + 3 + 2 bits to make a byte

                    switch (Idx)
                    { // The 4 first bytes wil be used to encode the size of the message
                        case 0: MessageSize = EncodedByte; break;
                        case 1: MessageSize = MessageSize + (EncodedByte << 8); break;
                        case 2: MessageSize = MessageSize + (EncodedByte << 16); break;
                        case 3: MessageSize = MessageSize + (EncodedByte << 32); break;
                        default: if (Idx - 4 < MessageSize) Result.WriteByte(EncodedByte); break;
                    }
                    Idx++;
                }
            }
        }
    }
    Result.Seek(0, System.IO.SeekOrigin.Begin);
    return Result;
}

You can download the solution here. Please, pay special attention to my high-level skills in GUI design... \m/

Thursday, August 22, 2013

Get ASCII codes from a VARCHAR SQL string

Today I faced a strange issue in SQL Server. I tried to select a specific row in SCCM table about OS Windows 7 Entreprise. So I typed the name of the OS in management studio, and ran a select on the proper table using a where clause on the OS name. Unexpectedly, no rows returned.
So I copied the name of the OS (from the SCCM table), and tested it against my own OS name. It can be summarized as below:

SELECT
  CASE WHEN 'Microsoft Windows 7 Entreprise' -- coming from SCCM
          = 'Microsoft Windows 7 Entreprise' -- what I typed
    THEN 'Equal'
    ELSE 'Not equal'
  END AS IsEqual
 

Wow... everyone who ever fought against special characters already knows the solution: a non-breakable space (or whatever) is present in one of the strings.

So I wrote a small query to inspect the ASCII characters of a string, and wished to share it. SO here it is:
 
DECLARE @Str VARCHAR(255) = 'Microsoft Windows 7 Entreprise' -- Coming from SCCM
;

WITH MyCounter AS
(
 SELECT 1 AS Idx
 UNION ALL
 SELECT a.Idx + 1 AS Idx
 FROM MyCounter a
 WHERE a.Idx < LEN(@Str)
)
SELECT Idx AS [Index], SUBSTRING(@Str, Idx, 1) AS [Character], ASCII(SUBSTRING(@Str, Idx, 1)) AS [AsciiCode]
FROM MyCounter
ORDER BY Idx asc
 

As expected, a non breakable space (ascii code 160) was present in SCCM table content, instead of standard whitespace (ascii code 32).

This small code can easily be turned into scalar valued function, if needed...
Hope it helps!
 

Monday, July 8, 2013

Find recursive dependencies between SQL objects

Ever needed to see the dependencies between objects in SQL Server?
Here is a trick to list all the objects (view, sp, function, etc) having dependencies on a given object (let's call it the root object). Of course, this code returns the dependencies of the dependencies in a recursive way, so that you can find all the objects that use the root, even if it is through intermediate objects.

Basically, it queries the system view sys.sql_expression_dependencies to get the objects using the root, it creates the level-1 dependencies. Then it iterates over the same system view to get all the objects using level-1 objects, creating level-2 dependencies. Next iteration creates set of level-3 dependencies, and so on, until no more dependencies are found.
Note that it could have been done with recursive-CTE too, but we would have to cope with tricky diamond-cases. So I chose the old-school solution, with iterations.

CREATE FUNCTION dbo.GetDependencies
(
 @SchemaName VARCHAR(255),
 @ObjectName VARCHAR(255)
)
RETURNS 
@Dependencies TABLE 
(
 Referenced INT NOT NULL,
 Referencing INT NOT NULL,
 Depth INT NOT NULL
)
AS
BEGIN
 DECLARE @CurrentDepth INT = 1
 DECLARE @NbRowsInserted INT

 INSERT INTO @Dependencies
 SELECT d.referenced_id, d.referencing_id, @CurrentDepth
 FROM sys.sql_expression_dependencies d
 WHERE d.referenced_id =
 (
  SELECT o.object_id
  FROM sys.objects o
  INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
  WHERE o.name = @ObjectName
  AND s.name = @SchemaName
 )
 AND d.referenced_id <> d.referencing_id

 SET @NbRowsInserted = @@ROWCOUNT

 WHILE (@NbRowsInserted > 0)
 BEGIN
  SET @CurrentDepth = @CurrentDepth + 1
  
  INSERT INTO @Dependencies
  SELECT d.referenced_id, d.referencing_id, @CurrentDepth
  FROM sys.sql_expression_dependencies d
  INNER JOIN
  (
   SELECT DISTINCT dd.Referencing
   FROM @Dependencies dd
   WHERE dd.Depth = @CurrentDepth - 1
  ) dd ON dd.Referencing = d.referenced_id
  LEFT JOIN
  (
   SELECT DISTINCT ddd.Referenced, ddd.Referencing
   FROM @Dependencies ddd
  ) ddd ON ddd.Referenced = d.referenced_id AND ddd.Referencing = d.referencing_id
  WHERE ddd.Referenced IS NULL /* Not a already referenced object, to avoid infinite loops */
  AND d.referenced_id <> d.referencing_id

  SET @NbRowsInserted = @@ROWCOUNT
 END
 
 RETURN 
END

As an example, let's create some dependent SQL objects, and ask for the dependencies:
CREATE TABLE dbo.RD_ROOT ( Foo INT, Bar INT)
GO
CREATE VIEW dbo.RD_View_1 AS SELECT Foo, Bar, Bar + 1 AS NewBar FROM dbo.RD_ROOT
GO
CREATE VIEW dbo.RD_View_2 AS SELECT Foo, NewBar * 2 AS NewNewBar FROM dbo.RD_VIEW_1
GO
CREATE PROCEDURE dbo.RD_SP_1 AS BEGIN SELECT Foo, NewBar FROM dbo.RD_View_1 END
GO
CREATE PROCEDURE dbo.RD_SP_2 AS BEGIN SELECT Foo, NewNewBar FROM dbo.RD_View_2 END
GO
SELECT *, OBJECT_NAME(Referenced) AS ReferencedName, OBJECT_NAME(Referencing) AS ReferencingName FROM TEC.FN_GET_DEPENDENCY('dbo', 'RD_ROOT')

It returns the following infos:
It just says that View_1 depends on Root, View_1 depends on View_2, SP_1 depends on View_1 too, and SP_2 depends on View_2.

Here is another way to display it, maybe a bit more readable, including the type of each object:
DECLARE @Dependencies TABLE 
(
 Referenced INT NOT NULL,
 Referencing INT NOT NULL,
 Depth INT NOT NULL
)

INSERT INTO @Dependencies
SELECT * FROM dbo.GetDependencies('dbo', 'RD_ROOT')

;

WITH RichDependencies AS
(
 SELECT
  d.Referencing AS ReferencingId,
  '[' + s.name + '].[' + o.name + '] (' + o.type + ')' AS ReferencingName,
  s.name AS ReferencingSchemaName,
  o.name AS ReferencingObjectName,
  o.type_desc AS ReferencingType,
  d.Depth AS DependencyDepth,
  d.Referenced AS ReferencedId
 FROM @Dependencies d
 INNER JOIN sys.objects o ON d.Referencing = o.object_id
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 UNION ALL
 SELECT
  o.object_id AS ReferencingId,
  '[' + s.name + '].[' + o.name + '] (' + o.type + ')' COLLATE SQL_Latin1_General_CP1_CI_AS AS ReferencingName,
  s.name AS ReferencingSchemaName,
  o.name AS ReferencingObjectName,
  o.type_desc AS ReferencingType,
  0 AS DependencyDepth,
  NULL AS ReferencedId
 FROM sys.objects o
 INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
 WHERE o.object_id = (SELECT MIN(Referenced) FROM @Dependencies WHERE Depth = 1)
)
SELECT
 d0.ReferencingName AS Level_0,
 d1.ReferencingName AS Level_1,
 d2.ReferencingName AS Level_2,
 d3.ReferencingName AS Level_3,
 d4.ReferencingName AS Level_4,
 d5.ReferencingName AS Level_5,
 d6.ReferencingName AS Level_6,
 d7.ReferencingName AS Level_7,
 d8.ReferencingName AS Level_8,
 d9.ReferencingName AS Level_9
FROM RichDependencies d0
LEFT JOIN RichDependencies d1 ON d1.ReferencedId = d0.ReferencingId AND d1.DependencyDepth = d0.DependencyDepth + 1
LEFT JOIN RichDependencies d2 ON d2.ReferencedId = d1.ReferencingId AND d2.DependencyDepth = d1.DependencyDepth + 1
LEFT JOIN RichDependencies d3 ON d3.ReferencedId = d2.ReferencingId AND d3.DependencyDepth = d2.DependencyDepth + 1
LEFT JOIN RichDependencies d4 ON d4.ReferencedId = d3.ReferencingId AND d4.DependencyDepth = d3.DependencyDepth + 1
LEFT JOIN RichDependencies d5 ON d5.ReferencedId = d4.ReferencingId AND d5.DependencyDepth = d4.DependencyDepth + 1
LEFT JOIN RichDependencies d6 ON d6.ReferencedId = d5.ReferencingId AND d6.DependencyDepth = d5.DependencyDepth + 1
LEFT JOIN RichDependencies d7 ON d7.ReferencedId = d6.ReferencingId AND d7.DependencyDepth = d6.DependencyDepth + 1
LEFT JOIN RichDependencies d8 ON d8.ReferencedId = d7.ReferencingId AND d8.DependencyDepth = d7.DependencyDepth + 1
LEFT JOIN RichDependencies d9 ON d9.ReferencedId = d8.ReferencingId AND d9.DependencyDepth = d8.DependencyDepth + 1
WHERE d0.DependencyDepth = 0
ORDER BY
 d0.ReferencingName, d1.ReferencingName, d2.ReferencingName, d3.ReferencingName, d4.ReferencingName, 
 d5.ReferencingName, d6.ReferencingName, d7.ReferencingName, d8.ReferencingName, d9.ReferencingName

Have fun!

Monday, April 8, 2013

Get partition bounds and number of rows

Sometimes, you need to have a synthetic view of the partition usage of a table.
For a specific table, the following query returns for each non-empty partition:
  • partition number
  • associated filegroup
  • number of rows of the partition
  • partition bounds (including RIGHT or LEFT inclusion)
  • number of rows in the partitions versus number of rows of the whole table (as a percentage)

SELECT
 '[' + s.name + '].[' + t.name + ']' AS TableName,
 p.partition_number AS PartitionNumber,
 CAST(prv_left.value AS VARCHAR)
 + CASE pf.boundary_value_on_right WHEN 1 THEN ' <= X < ' ELSE ' < X <= ' END
 + CAST(prv_right.value AS VARCHAR) AS PartitionBounds,
 p.[rows] AS PartitionNumberOfRows,
 fg.name AS PartitionFilegroup,
 CAST((100 * p.[rows]) / ii.[rows] AS VARCHAR) + '%' AS SizeRatio
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i ON i.object_id = t.object_id
INNER JOIN sysindexes ii ON ii.id = t.object_id
INNER JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id = i.index_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
LEFT JOIN sys.partition_range_values prv_left
 ON prv_left.function_id = pf.function_id
 AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values prv_right
 ON prv_right.function_id = pf.function_id
 AND prv_right.boundary_id = p.partition_number
WHERE i.[type] IN (0, 1) /* Index of type HEAP or CLUSTERED */
AND s.name = 'your_schema_name'
AND t.name = 'your_table_name'
AND p.[rows] > 0
AND ii.indid IN (0, 1)
ORDER BY s.name, t.name, prv_right.value


This may be useful to detect space-consuming partitions, to ensure partition distribution is homogenous, to ensure filegroups are correctly mapped over the partition bounds, etc.

Duplicate table structure and indexes for switch partition

When you want to perform a SWITCH PARTITION statement, you need to duplicate the table structure you are switching. See this article for more details about partitioning.
As far as I know, there is no such a native capability in SQL Server. You may try
SELECT * INTO <your_switch_table> FROM <your_original_table>

But this will only copy the list of columns, not the indexes that are needed for partition switch. What is more, the table will be created in default filegroup, where you would prefer the partitioned filegroup.

So I wrote a stored procedure that creates a copy of the table structure, indexes (clustered an non-clustered), and allows some options for the storage filegroup.


/*
This stored procedure generates a SQL script that creates a copy of SQL
table and indexes. Some options allow to fine-tune the generated script,
like include indexes or not, change the filegroup of table, etc.
*/
CREATE PROCEDURE SCRIPT_CREATE_TABLE
 @OriginalSchemaName SYSNAME,
 @OriginalTableName SYSNAME,
 @TargetSchemaName SYSNAME,
 @TargetTableName SYSNAME,
 @OutputQuery VARCHAR(MAX) OUTPUT,
 @FilegroupToUse SYSNAME = NULL,
 @ScriptIndexes BIT = 1
AS
BEGIN

/*
 References
 http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table
 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=125819
 http://ranjithk.com/2010/01/31/how-to-determine-the-file-group-of-a-table-or-a-index/
 http://stackoverflow.com/questions/1121522/generate-create-scripts-for-a-list-of-indexes
*/

BEGIN TRY


/* ******************************** */
/* FIND THE OBJECT_ID CORRESPONDING */
/* TO THE ORIGINAL TABLE            */
/* ******************************** */
SET @OutputQuery = ''
DECLARE @Msg VARCHAR(MAX)
DECLARE @TableId INT = NULL

SELECT @TableId = t.object_id
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON c.object_id = t.object_id
WHERE s.name = @OriginalSchemaName
AND t.name = @OriginalTableName

IF @TableId IS NULL
 BEGIN
 SET @Msg = 'Cannot find table [' + @OriginalSchemaName + '].[' + @OriginalTableName + ']'
 RAISERROR(@Msg, 16, 1)
 END




/* ************************************* */
/* FIND THE FILEGROUP / PARTITION SCHEME */
/* TO BE USED IN TARGET TABLE            */
/* ************************************* */

DECLARE @TargetFilegroup VARCHAR(MAX) = NULL
IF @FilegroupToUse IS NULL OR @FilegroupToUse = ''
 /* No filegroup specified, find the original one */
 BEGIN
 
 DECLARE @OriginalFilegroup VARCHAR(MAX) = NULL
 
 /* First, try to find a partition scheme used by the table */
 SELECT @OriginalFilegroup = '[' + ps.name + ']([' + c.name + '])'
 FROM sys.tables t
 INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
 INNER JOIN sys.indexes i ON i.object_id = t.object_id
 INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
 INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
 INNER JOIN sys.index_columns ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id
 INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
 WHERE i.[type] IN (0, 1) /* Index of type HEAP or CLUSTERED */
 AND s.name = @OriginalSchemaName
 AND t.name = @OriginalTableName
 
 /* If no partition shceme found, try to find a regular filegroup */
 IF @OriginalFilegroup IS NULL
  BEGIN
  SELECT @OriginalFilegroup = '[' + d.name + ']'
  FROM sys.filegroups d
  INNER JOIN sys.indexes i ON i.data_space_id = d.data_space_id
  INNER JOIN sys.tables t ON t.object_id = i.object_id
  WHERE i.index_id < 2
  AND t.name = @OriginalTableName
  AND t.schema_id = schema_id(@OriginalSchemaName)
  END

 /* No regular filegroup neither partition shceme found, there is a problem */
 IF @OriginalFilegroup IS NULL
  BEGIN
  SET @Msg = 'Could not find filegroup neither partition scheme for table [' + @OriginalSchemaName + '].[' + @OriginalTableName + ']'
  RAISERROR(@Msg, 16, 1)
  END
  
 SET @TargetFilegroup = @OriginalFilegroup
 
 END
ELSE
 /* A filegroup has been specified, just use this one */
 BEGIN
 SET @TargetFilegroup = @FilegroupToUse
 END




/* ******************************* */
/* GENERATE CREATE TABLE STATEMENT */
/* ******************************* */

DECLARE @SqlQuery_CreateTable VARCHAR(MAX)
SELECT  @SqlQuery_CreateTable = 'CREATE TABLE [' + @TargetSchemaName + '].[' + @TargetTableName + ']
(
' + o.list +
') ON ' + @TargetFilegroup + '
WITH ( DATA_COMPRESSION = ' + CompressionMode + ')
' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE [' + @TargetSchemaName + '].[' + @TargetTableName + '] ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY (' + LEFT(j.List, Len(j.List)-1) + ')' END
FROM sysobjects so
INNER JOIN
(
 SELECT p.object_id, MIN(data_compression_desc) AS CompressionMode
 FROM sys.partitions p
 WHERE p.object_id = @TableId
 AND p.index_id < 2
 GROUP BY p.object_id
) p ON p.object_id = so.id
CROSS APPLY
(
 SELECT '  [' + column_name + '] ' + data_type + 
  CASE data_type
            WHEN 'sql_variant' THEN ''
            WHEN 'text' THEN ''
            WHEN 'decimal' THEN '(' + cast(numeric_precision_radix AS VARCHAR) + ', ' + CAST(numeric_scale AS VARCHAR) + ')'
            ELSE COALESCE('('+ CASE WHEN character_maximum_length = -1 THEN 'MAX' ELSE CAST(character_maximum_length AS VARCHAR) END +')', '')
   END + ' ' +
        CASE
   WHEN EXISTS
    ( 
     SELECT id FROM syscolumns
     WHERE object_name(id) = so.name
     AND name = column_name
     AND columnproperty(id,name, 'IsIdentity') = 1 
    )
    THEN 'IDENTITY(' + CAST(IDENT_SEED(so.name) AS VARCHAR) + ', ' + CAST(IDENT_INCR(so.name) AS VARCHAR) + ') '
   ELSE ''
   END +
         (CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END ) + 'NULL' + 
          CASE WHEN information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN ' DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ',' + CHAR(10)
     FROM information_schema.columns
     WHERE table_name = so.name
     ORDER BY ordinal_position
     FOR XML PATH('')
) o (list)
LEFT JOIN information_schema.table_constraints tc
 ON tc.Table_name = so.Name AND tc.Constraint_Type = 'PRIMARY KEY'
CROSS APPLY
(
 SELECT '[' + Column_Name + '], '
 FROM information_schema.key_column_usage kcu
 WHERE kcu.Constraint_Name = tc.Constraint_Name
 ORDER BY ORDINAL_POSITION
 FOR XML PATH('')
) j (list)
WHERE so.xtype = 'U'
AND so.name NOT IN ('dtproperties')
AND so.id = @TableId

SET @OutputQuery = @OutputQuery + @SqlQuery_CreateTable



/* ******************************** */
/* GENERATE CREATE INDEX STATEMENTS */
/* ******************************** */
IF @ScriptIndexes = 1
 BEGIN
 DECLARE @SqlQuery_CreateIndex VARCHAR(MAX)
 ;
 WITH IndexCTE AS
 (
  SELECT DISTINCT
   i.index_id,
   i.name,
   i.object_id,
   p.[data_compression_desc] COLLATE SQL_Latin1_General_CP1_CI_AS AS CompressionMode,
   ifg.IndexFilegroup,
   i.is_unique
  FROM sys.indexes i
  INNER JOIN sys.tables t ON t.object_id = i.object_id
  INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
  INNER JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id
  INNER JOIN
  (
   SELECT p.object_id, p.index_id, MIN(p.[data_compression_desc]) AS [data_compression_desc]
   FROM sys.partitions p
   GROUP BY p.object_id, p.index_id
  ) p ON p.index_id = i.index_id AND p.object_id = t.object_id
  INNER JOIN /* Find the filegroup or partition scheme on which the index relies */
  (
   SELECT i.index_id, i.name, '[' + ps.name + ']([' + c.name + '])' AS IndexFilegroup
   FROM sys.tables t
   INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
   INNER JOIN sys.indexes i ON i.object_id = t.object_id
   INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
   INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
   INNER JOIN sys.index_columns ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id
   INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
   WHERE 1 = 1
   AND s.name = @OriginalSchemaName
   AND t.name = @OriginalTableName
  UNION ALL
   SELECT i.index_id, i.name, '[' + d.name + ']' AS IndexFilegroup
   FROM sys.filegroups d
   INNER JOIN sys.indexes i ON i.data_space_id = d.data_space_id
   INNER JOIN sys.tables t ON t.object_id = i.object_id
   WHERE 1 = 1
   AND t.name = @OriginalTableName
   AND t.schema_id = schema_id(@OriginalSchemaName)
  ) ifg ON ifg.index_id = p.index_id
  WHERE EXISTS
  (
   SELECT *
   FROM sys.columns c
   WHERE c.column_id = ic.column_id
   AND c.object_id = ic.object_id
  )
  AND t.name = @OriginalTableName
  AND s.name = @OriginalSchemaName
 ), 
 IndexCTE2 AS
 (
  SELECT 
   IndexCTE.name AS IndexName,
   CASE IndexCTE.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END AS IndexUnicity,
   CASE IndexCTE.index_id WHEN 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END AS IndexType,
   (
    SELECT CHAR(10) + '  ' + c.name + CASE ic.is_descending_key WHEN 0 THEN ' ASC' ELSE ' DESC' END + ','
    FROM sys.columns c
    INNER JOIN sys.index_columns ic
     ON c.object_id = ic.object_id
     AND ic.column_id = c.column_id
     AND ic.Is_Included_Column = 0
    WHERE IndexCTE.OBJECT_ID = ic.object_id
    AND IndexCTE.index_id = ic.index_id
    AND ic.key_ordinal > 0
    ORDER BY key_ordinal
    FOR XML PATH('')
   ) ixcols,
   ISNULL(
   (
    SELECT DISTINCT CHAR(10) + '  ' + c.name + ','
    FROM sys.columns c
    INNER JOIN sys.index_columns ic
     ON c.object_id = ic.object_id
     AND ic.column_id = c.column_id
     AND ic.Is_Included_Column = 1
    WHERE IndexCTE.OBJECT_ID = ic.object_id 
    AND IndexCTE.index_id = ic.index_id 
    FOR XML PATH('')
   ), '') includedcols,
   IndexCTE.index_id,
   IndexCTE.CompressionMode,
   IndexCTE.IndexFilegroup
  FROM indexCTE
 ),
 CreateSingleIndexQuery AS
 ( 
 SELECT 
  'CREATE ' + IndexUnicity + IndexType + ' INDEX [' + IndexName + '] ON [' + @TargetSchemaName + '].[' + @TargetTableName + ']' + CHAR(10) +
  '(' + SUBSTRING(ixcols, 1, LEN(ixcols) - 1) + CHAR(10) +
  CASE LEN(includedcols)
   WHEN 0 THEN ')'
   ELSE ')' + CHAR(10) + 'INCLUDE' + CHAR(10) + '(' + SUBSTRING(includedcols, 1, LEN(includedcols) - 1) + CHAR(10) + ')'
  END + CHAR(10) +
  'WITH ( DATA_COMPRESSION = ' + CompressionMode + ' )' + CHAR(10) +
  'ON ' + IndexFilegroup AS Query,
  index_id
 FROM IndexCTE2
 )
 SELECT @SqlQuery_CreateIndex = o.list
 FROM CreateSingleIndexQuery
 CROSS APPLY
 (
  SELECT CHAR(10) + Query + CHAR(10)
  FROM CreateSingleIndexQuery
  ORDER BY index_id
  FOR XML PATH('')
 ) o (list)


 SET @OutputQuery = @OutputQuery + @SqlQuery_CreateIndex
 END


END TRY

BEGIN CATCH
  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

END

Easy to use:
DECLARE @SqlQuery VARCHAR(4000) = ''
EXEC SPR_SCRIPT_CREATE_TABLE
  'dbo', 'existing_table',
  'dbo', 'switch_table',
  @SqlQuery OUTPUT

EXEC(@SqlQuery)