Get a list of databases from SQL Server

Option 1: SQL Connection

            Dim sqlsb As New SqlClient.SqlConnectionStringBuilder()
            sqlsb.ConnectionString = connString '_connectionString
            sqlsb.InitialCatalog = ""

            Using conDB As New SqlConnection(sqlsb.ToString())
                conDB.Open()
                dtDatabases = conDB.GetSchema("Databases")
                conDB.Close()
                '   *** cmbDatabase.Items.Clear()
                For Each r As DataRow In dtDatabases.Rows
                    Select Case r("database_name").ToString
                        Case "master", "tempdb", "msdb", "model"
                        Case Else
                            ' **** mbDatabase.Items.Add(r("database_name"))
                            names.Add(r("database_name"))
                    End Select
                Next
            End Using

Option 2: SQL Select:

SELECT DB_NAME(database_id) AS [Database], database_id
FROM sys.databases
WHERE database_id>4

Option 3: Microsoft SMO Objects

Install-Package Microsoft.SqlServer.SqlManagementObjects -Version 140.17199.0


var SDBLOC = new Microsoft.SqlServer.Management.Smo.Server("localhost").Datab‌​ases.Cast<Microsoft.‌​SqlServer.Management‌​.Smo.Database>().Whe‌​re(bs => !bs.IsSystemObject && bs.ID>6).ToList();

Advertisements

How to install .NET Framework 3.5 on Windows Server 2012 and Windows Server 2012 R2

If you have an application that you want to run on Windows Server 2012 that requires the .NET Framework 3.5, you will most likely run in to a problem when trying to install it. If you are trying to install .NET Framework 3.5 from the Server Manager GUI, you will see this when installing the feature:

“Do you want to specify an alternate source path? One or more installation selections are missing source files…”

To solve this, you can either:

Powershell (As Admin)

Install-WindowsFeature Net-Framework-Core -source \\network\share\sxs

Old Fashion Command Line (As Admin)

DISM /Online /Enable-Feature /FeatureName:NetFx3 /All /LimitAccess /Source:d:\sources\sxs

Using Powershell you can verify the install by running Get-WindowsFeature from within PS, you will notice something similar to this;

[X] .NET Framework 3.5 Features NET-Framework-Features   Installed
[X] .NET Framework 3.5 (includes .NET 2.0 and 3.0)  NET-Framework-Core Installed

Note: Source should be the Windows installation disc. In my case, this was located on D:

Bug when adding .net framework 3.5 in Server 2012

2. Go down to “Specify an alternate source path” and enter “d:\sources\sxs” as the path.

Now you should see this under your Features list:

.NET Framework 3.5 feature installed on Windows Server 2012

See currently running queries in SQL Server 2012

SELECT r.start_time [Start Time],session_ID [SPID],
DB_NAME(database_id) [Database],
SUBSTRING(t.text,(r.statement_start_offset/2)+1,
CASE WHEN statement_end_offset=-1 OR statement_end_offset=0
THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1
ELSE (r.statement_end_offset-r.statement_start_offset)/2+1
END) [Executing SQL],
Status,command,wait_type,wait_time,wait_resource,
last_wait_type
FROM sys.dm_exec_requests r
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id != @@SPID — don’t show this query
AND session_id > 50 — don’t show system queries
ORDER BY r.start_time

This query is great at finding locking queries like the one below:

To fix this, either add “(NOLOCK)” table hint to allow the select to run while the update is happening or kill the offending process using “KILL” command followed by the SPID

How to check if a file is an image

You can always check the extension to be “jpg, jpeg, gif, tiff, png, bmp” but sometimes, malicious attackers can upload an exe file with the wrong extension and then use a series of commands to remove the extension/run the file on the server.

How to check extension:

public static readonly List ImageExtensions = new List { ".JPG", ".JPE", ".BMP", ".GIF", ".PNG" };

private void button_Click(object sender, RoutedEventArgs e)
{
    var folder = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
    var files = Directory.GetFiles(folder);
    foreach(var f in files)
    {
        if (ImageExtensions.Contains(Path.GetExtension(f).ToUpperInvariant()))
        {
            // process image
        }
    }
}

The other option would be .NET 4.5:
MimeMapping.GetMimeMapping Method

Or:

static Extension()
    {
        ImageTypes = new Dictionary();
        ImageTypes.Add("FFD8","jpg");
        ImageTypes.Add("424D","bmp");
        ImageTypes.Add("474946","gif");
        ImageTypes.Add("89504E470D0A1A0A","png");
    }

    /// 
    ///      Registers a hexadecimal value used for a given image type 
    ///      The type of image, example: "png" 
    ///      The type of image, example: "89504E470D0A1A0A" 
    /// 
    public static void RegisterImageHeaderSignature(string imageType, string uniqueHeaderAsHex)
    {
        Regex validator = new Regex(@"^[A-F0-9]+$", RegexOptions.CultureInvariant);

        uniqueHeaderAsHex = uniqueHeaderAsHex.Replace(" ", "");

        if (string.IsNullOrWhiteSpace(imageType))         throw new ArgumentNullException("imageType");
        if (string.IsNullOrWhiteSpace(uniqueHeaderAsHex)) throw new ArgumentNullException("uniqueHeaderAsHex");
        if (uniqueHeaderAsHex.Length % 2 != 0)            throw new ArgumentException    ("Hexadecimal value is invalid");
        if (!validator.IsMatch(uniqueHeaderAsHex))        throw new ArgumentException    ("Hexadecimal value is invalid");

        ImageTypes.Add(uniqueHeaderAsHex, imageType);
    }

    private static Dictionary ImageTypes;

    public static bool IsImage(this Stream stream)
    {
        string imageType;
        return stream.IsImage(out imageType);
    }

    public static bool IsImage(this Stream stream, out string imageType)
    {
        stream.Seek(0, SeekOrigin.Begin);
        StringBuilder builder = new StringBuilder();
        int largestByteHeader = ImageTypes.Max(img => img.Value.Length);

        for (int i = 0; i  img == builtHex);
            if (isImage)
            {
                imageType = ImageTypes[builder.ToString()];
                return true;
            }
        }
        imageType = null;
        return false;
    }

Script all indexes for all tables in a database (SQL Script)

The need often arises to create or recreate the indexes for all tables in a database, especially in development and testing scenarios. This article presents a script to generate Index Creation Scripts for all tables in a database usingTransact-SQL (T-SQL).

SELECT ' CREATE ' +  
    CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  +   
    I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +    
    I.name  + ' ON '  +   
    Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +  
    KeyColumns + ' )  ' +  
    ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') +  
    ISNULL(' WHERE  '+I.Filter_definition,'') + ' WITH ( ' +  
    CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ','  +  
    'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ','  +  
    -- default value  
    'SORT_IN_TEMPDB = OFF '  + ','  +  
    CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ','  +  
    CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ','  +  
    -- default value   
    ' DROP_EXISTING = ON '  + ','  +  
    -- default value   
    ' ONLINE = OFF '  + ','  +  
   CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ','  +  
   CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END  + ' ) ON [' +  
   DS.name + ' ] '  [CreateIndexScript]  
FROM sys.indexes I    
 JOIN sys.tables T ON T.Object_id = I.Object_id     
 JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid    
 JOIN (SELECT * FROM (   
    SELECT IC2.object_id , IC2.index_id ,   
        STUFF((SELECT ' , ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END 
    FROM sys.index_columns IC1   
    JOIN Sys.columns C    
       ON C.object_id = IC1.object_id    
       AND C.column_id = IC1.column_id    
       AND IC1.is_included_column = 0   
    WHERE IC1.object_id = IC2.object_id    
       AND IC1.index_id = IC2.index_id    
    GROUP BY IC1.object_id,C.name,index_id   
    ORDER BY MAX(IC1.key_ordinal)   
       FOR XML PATH('')), 1, 2, '') KeyColumns    
    FROM sys.index_columns IC2    
    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables   
    GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4    
  ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id   
 JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id    
 JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id    
 JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id    
 LEFT JOIN (SELECT * FROM (    
    SELECT IC2.object_id , IC2.index_id ,    
        STUFF((SELECT ' , ' + C.name  
    FROM sys.index_columns IC1    
    JOIN Sys.columns C     
       ON C.object_id = IC1.object_id     
       AND C.column_id = IC1.column_id     
       AND IC1.is_included_column = 1    
    WHERE IC1.object_id = IC2.object_id     
       AND IC1.index_id = IC2.index_id     
    GROUP BY IC1.object_id,C.name,index_id    
       FOR XML PATH('')), 1, 2, '') IncludedColumns     
   FROM sys.index_columns IC2     
   --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables    
   GROUP BY IC2.object_id ,IC2.index_id) tmp1    
   WHERE IncludedColumns IS NOT NULL ) tmp2     
ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id    
WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0  
--AND I.Object_id = object_id('Person.Address') --Comment for all tables  
--AND I.name = 'IX_Address_PostalCode' --comment for all indexes