SELECT
t.NAME AS TableName,
i.name as indexName,
p.[Rows],
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY
p.[Rows] desc
Programming is fun and interesting. As you all know, sometime we programmers stucks in the middle way of coding without getting a solution. Blogs like this will help us to find a path to solve our problems. The contents of this blog are the solutions that I found by myself and from other sources. Sharing such solutions is also interesting. Thank you!
Wednesday, March 18, 2015
Way to find row details of all tables in a DB - SQL
Monday, March 16, 2015
Simple two-way encryption for C#
Simple AES
using System;
using System.Data;
using System.Security.Cryptography;
using System.IO;
public class SimpleAES
{
// Change these keys
private byte[] Key = { 123, 217, 19, 11, 24, 26, 85, 45, 114, 184, 27, 162, 37, 112, 222, 209, 241, 24, 175, 144, 173, 53, 196, 29, 24, 26, 17, 218, 131, 236, 53, 209 };
private byte[] Vector = { 146, 64, 191, 111, 23, 3, 113, 119, 231, 121, 2521, 112, 79, 32, 114, 156 };
private ICryptoTransform EncryptorTransform, DecryptorTransform;
private System.Text.UTF8Encoding UTFEncoder;
public SimpleAES()
{
//This is our encryption method
RijndaelManaged rm = new RijndaelManaged();
//Create an encryptor and a decryptor using our encryption method, key, and vector.
EncryptorTransform = rm.CreateEncryptor(this.Key, this.Vector);
DecryptorTransform = rm.CreateDecryptor(this.Key, this.Vector);
//Used to translate bytes to text and vice versa
UTFEncoder = new System.Text.UTF8Encoding();
}
/// -------------- Two Utility Methods (not used but may be useful) -----------
/// Generates an encryption key.
static public byte[] GenerateEncryptionKey()
{
//Generate a Key.
RijndaelManaged rm = new RijndaelManaged();
rm.GenerateKey();
return rm.Key;
}
/// Generates a unique encryption vector
static public byte[] GenerateEncryptionVector()
{
//Generate a Vector
RijndaelManaged rm = new RijndaelManaged();
rm.GenerateIV();
return rm.IV;
}
/// ----------- The commonly used methods ------------------------------
/// Encrypt some text and return a string suitable for passing in a URL.
public string EncryptToString(string TextValue)
{
return ByteArrToString(Encrypt(TextValue));
}
/// Encrypt some text and return an encrypted byte array.
public byte[] Encrypt(string TextValue)
{
//Translates our text value into a byte array.
Byte[] bytes = UTFEncoder.GetBytes(TextValue);
//Used to stream the data in and out of the CryptoStream.
MemoryStream memoryStream = new MemoryStream();
/*
* We will have to write the unencrypted bytes to the stream,
* then read the encrypted result back from the stream.
*/
#region Write the decrypted value to the encryption stream
CryptoStream cs = new CryptoStream(memoryStream, EncryptorTransform, CryptoStreamMode.Write);
cs.Write(bytes, 0, bytes.Length);
cs.FlushFinalBlock();
#endregion
#region Read encrypted value back out of the stream
memoryStream.Position = 0;
byte[] encrypted = new byte[memoryStream.Length];
memoryStream.Read(encrypted, 0, encrypted.Length);
#endregion
//Clean up.
cs.Close();
memoryStream.Close();
return encrypted;
}
/// The other side: Decryption methods
public string DecryptString(string EncryptedString)
{
return Decrypt(StrToByteArray(EncryptedString));
}
/// Decryption when working with byte arrays.
public string Decrypt(byte[] EncryptedValue)
{
#region Write the encrypted value to the decryption stream
MemoryStream encryptedStream = new MemoryStream();
CryptoStream decryptStream = new CryptoStream(encryptedStream, DecryptorTransform, CryptoStreamMode.Write);
decryptStream.Write(EncryptedValue, 0, EncryptedValue.Length);
decryptStream.FlushFinalBlock();
#endregion
#region Read the decrypted value from the stream.
encryptedStream.Position = 0;
Byte[] decryptedBytes = new Byte[encryptedStream.Length];
encryptedStream.Read(decryptedBytes, 0, decryptedBytes.Length);
encryptedStream.Close();
#endregion
return UTFEncoder.GetString(decryptedBytes);
}
/// Convert a string to a byte array. NOTE: Normally we'd create a Byte Array from a string using an ASCII encoding (like so).
// System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
// return encoding.GetBytes(str);
// However, this results in character values that cannot be passed in a URL. So, instead, I just
// lay out all of the byte values in a long string of numbers (three per - must pad numbers less than 100).
public byte[] StrToByteArray(string str)
{
if (str.Length == 0)
throw new Exception("Invalid string value in StrToByteArray");
byte val;
byte[] byteArr = new byte[str.Length / 3];
int i = 0;
int j = 0;
do
{
val = byte.Parse(str.Substring(i, 3));
byteArr[j++] = val;
i += 3;
}
while (i < str.Length);
return byteArr;
}
// Same comment as above. Normally the conversion would use an ASCII encoding in the other direction:
// System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding();
// return enc.GetString(byteArr);
public string ByteArrToString(byte[] byteArr)
{
byte val;
string tempStr = "";
for (int i = 0; i <= byteArr.GetUpperBound(0); i++)
{
val = byteArr[i];
if (val < (byte)10)
tempStr += "00" + val.ToString();
else if (val < (byte)100)
tempStr += "0" + val.ToString();
else
tempStr += val.ToString();
}
return tempStr;
}
}
AES Encryption / Decryption for JAVA
package org.ferris.aes.crypto;
import java.io.UnsupportedEncodingException;
import java.security.Key;
import java.security.spec.KeySpec;
import javax.crypto.Cipher;
import javax.crypto.SecretKey;
import javax.crypto.SecretKeyFactory;
import javax.crypto.spec.IvParameterSpec;
import javax.crypto.spec.PBEKeySpec;
import javax.crypto.spec.SecretKeySpec;
import org.apache.commons.codec.binary.Base64;
/**
*
*
*/
public class AesBase64Wrapper {
private static String IV = "IV_VALUE_16_BYTE";
private static String PASSWORD = "PASSWORD_VALUE";
private static String SALT = "SALT_VALUE";
public String encryptAndEncode(String raw) {
try {
Cipher c = getCipher(Cipher.ENCRYPT_MODE);
byte[] encryptedVal = c.doFinal(getBytes(raw));
String s = getString(Base64.encodeBase64(encryptedVal));
return s;
} catch (Throwable t) {
throw new RuntimeException(t);
}
}
public String decodeAndDecrypt(String encrypted) throws Exception {
byte[] decodedValue = Base64.decodeBase64(getBytes(encrypted));
Cipher c = getCipher(Cipher.DECRYPT_MODE);
byte[] decValue = c.doFinal(decodedValue);
return new String(decValue);
}
private String getString(byte[] bytes) throws UnsupportedEncodingException {
return new String(bytes, "UTF-8");
}
private byte[] getBytes(String str) throws UnsupportedEncodingException {
return str.getBytes("UTF-8");
}
private Cipher getCipher(int mode) throws Exception {
Cipher c = Cipher.getInstance("AES/CBC/PKCS5Padding");
byte[] iv = getBytes(IV);
c.init(mode, generateKey(), new IvParameterSpec(iv));
return c;
}
private Key generateKey() throws Exception {
SecretKeyFactory factory = SecretKeyFactory.getInstance("PBKDF2WithHmacSHA1");
char[] password = PASSWORD.toCharArray();
byte[] salt = getBytes(SALT);
KeySpec spec = new PBEKeySpec(password, salt, 65536, 128);
SecretKey tmp = factory.generateSecret(spec);
byte[] encoded = tmp.getEncoded();
return new SecretKeySpec(encoded, "AES");
}
}
AES Encryption / Decryption for .NET(C#)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
namespace EncryptDecryptTest
{
class Program
{
class AesBase64Wrapper
{
private static string IV = "IV_VALUE_16_BYTE";
private static string PASSWORD = "PASSWORD_VALUE";
private static string SALT = "SALT_VALUE";
public static string EncryptAndEncode(string raw)
{
using (var csp = new AesCryptoServiceProvider())
{
ICryptoTransform e = GetCryptoTransform(csp, true);
byte[] inputBuffer = Encoding.UTF8.GetBytes(raw);
byte[] output = e.TransformFinalBlock(inputBuffer, 0, inputBuffer.Length);
string encrypted = Convert.ToBase64String(output);
return encrypted;
}
}
public static string DecodeAndDecrypt(string encrypted)
{
using (var csp = new AesCryptoServiceProvider())
{
var d = GetCryptoTransform(csp, false);
byte[] output = Convert.FromBase64String(encrypted);
byte[] decryptedOutput = d.TransformFinalBlock(output, 0, output.Length);
string decypted = Encoding.UTF8.GetString(decryptedOutput);
return decypted;
}
}
private static ICryptoTransform GetCryptoTransform(AesCryptoServiceProvider csp, bool encrypting)
{
csp.Mode = CipherMode.CBC;
csp.Padding = PaddingMode.PKCS7;
var spec = new Rfc2898DeriveBytes(Encoding.UTF8.GetBytes(PASSWORD), Encoding.UTF8.GetBytes(SALT), 65536);
byte[] key = spec.GetBytes(16);
csp.IV = Encoding.UTF8.GetBytes(IV);
csp.Key = key;
if (encrypting)
{
return csp.CreateEncryptor();
}
return csp.CreateDecryptor();
}
}
static void Main(string[] args)
{
string encryptMe;
string encrypted;
string decrypted;
encryptMe = "please encrypt me";
Console.WriteLine("encryptMe = " + encryptMe);
encrypted = AesBase64Wrapper.EncryptAndEncode(encryptMe);
Console.WriteLine("encypted: " + encrypted);
decrypted = AesBase64Wrapper.DecodeAndDecrypt(encrypted);
Console.WriteLine("decrypted: " + decrypted);
Console.WriteLine("press any key to exit....");
Console.ReadKey();
}
}
}
Subscribe to:
Posts (Atom)