Tag Archive for sql

Simple SQL Server Diff Tool

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;

namespace Sdiff
{
    internal class Program
    {
        /// <param name = "args">arg[0] old schema connection string, arg[1] target schema connection string</param>
        /// <summary>
        ///   Tells you what to do with old schema to get it into the shape of the new
        /// </summary>
        private static void Main(string[] args)
        {
            var diffs = new List<Diff>();

            var oldContext = new IsDataContext(args[0]);
            var newContext = new IsDataContext(args[1]);

            var newTableNames = newContext.Tables.Select(x => x.Name).ToList();
            var oldTableNames = oldContext.Tables.Select(x => x.Name).ToList();

            AddDiffs(diffs, oldTableNames.Except(newTableNames), DiffKind.TableDelete);
            AddDiffs(diffs, newTableNames.Except(oldTableNames), DiffKind.TableCreate);

            var commonTableNames = newTableNames.Intersect(oldTableNames);
            foreach (var tableName in commonTableNames)
            {
                var newColumns = newContext.Columns.Where(x => x.TableName == tableName).ToList();
                var oldColumns = oldContext.Columns.Where(x => x.TableName == tableName).ToList();

                var newColumnNames = newColumns.Select(x => x.FullName);
                var oldColumnNames = oldColumns.Select(x => x.FullName);

                AddDiffs(diffs, oldColumnNames.Except(newColumnNames), DiffKind.ColumnDelete);
                AddDiffs(diffs, newColumnNames.Except(oldColumnNames), DiffKind.ColumnCreate);

                var commonColumnNames = newColumnNames.Intersect(oldColumnNames);
                foreach (var commonColumnName in commonColumnNames)
                {
                    var newDataType = newColumns.Where(x => x.FullName == commonColumnName).Select(x => x.DataType).Single();
                    var oldDataType = oldColumns.Where(x => x.FullName == commonColumnName).Select(x => x.DataType).Single();
                    if (oldDataType != newDataType) diffs.Add(new Diff(commonColumnName + " " + oldDataType + " -> " + newDataType, DiffKind.ColumnDataTypeChange));
                }
            }

            WriteSection(diffs.Where(x => x.Kind == DiffKind.TableDelete), "Tables to delete");
            WriteSection(diffs.Where(x => x.Kind == DiffKind.TableCreate), "Tables to create");
            WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnDelete), "Columns to delete");
            WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnCreate), "Columns to create");
            WriteSection(diffs.Where(x => x.Kind == DiffKind.ColumnDataTypeChange), "Columns to modify");

            Console.ReadKey();
        }

        private static void AddDiffs(List<Diff> diffs, IEnumerable<string> names, DiffKind diffKind)
        {
            diffs.AddRange(names.Select(name => new Diff(name, diffKind)));
        }


        private static void WriteSection(IEnumerable<Diff> diffs, string title)
        {
            if (!diffs.Any()) return;
            Console.WriteLine();
            Console.WriteLine(title);
            Console.WriteLine(string.Empty.PadLeft(title.Length, '-'));
            foreach (var name in diffs.Select(x => x.Name).OrderBy(x => x))
            {
                Console.WriteLine(name);
            }
        }

        private class Diff
        {
            public Diff(string name, DiffKind kind)
            {
                Name = name;
                Kind = kind;
            }

            public string Name { get; private set; }
            public DiffKind Kind { get; private set; }
        }

        internal enum DiffKind
        {
            TableDelete,
            TableCreate,
            ColumnDelete,
            ColumnCreate,
            ColumnDataTypeChange
        }
    }


    public class IsDataContext : DataContext
    {
        public Table<Column> Columns;
        public Table<Table> Tables;
        public IsDataContext(string connection) : base(connection) {}
    }

    [Table(Name = "INFORMATION_SCHEMA.TABLES")]
    public class Table
    {
        [Column(Name = "TABLE_NAME")] public string Name;
    }

    [Table(Name = "INFORMATION_SCHEMA.COLUMNS")]
    public class Column
    {
        [Column(Name = "CHARACTER_MAXIMUM_LENGTH")] public int? CharacterMaximumLength;
        [Column(Name = "DATA_TYPE")] public string DataTypeName;
        [Column(Name = "COLUMN_NAME")] public string Name;
        [Column(Name = "TABLE_NAME")] public string TableName;

        public string DataType
        {
            get
            {
                if (!CharacterMaximumLength.HasValue) return DataTypeName;
                if (CharacterMaximumLength.Value == -1) return DataTypeName + "(MAX)";
                return DataTypeName + "(" + CharacterMaximumLength.Value + ")";
            }
        }

        public string FullName { get { return TableName + "." + Name; } }
    }
}

source

Oracle TimeStamp to Char conversion

SELECT TO_CHAR(CURRENT_TIMESTAMP,'YYYYMMDDHH24MISSFF') from dual;


Result:
------
20100701162937883230

source

Oracle – Overlaps function

-- Overlap present

select 'YES' as overlap from dual
    where          (date '2007-01-01', date '2008-01-01')
          overlaps (date '2005-01-01', date '2009-01-01')


OVE
---
YES

-- Overlap not present

select 'YES' as overlap from dual
    where          (date '2007-01-01', date '2008-01-01')
          overlaps (date '2005-01-01', date '2006-01-01')


no rows selected.

source

Ranking Function – Range over partition – Oracle Analytical function

-- Example 1
ROW_NUMBER () OVER (PARTITION BY A.LN ORDER BY A.FC_SET_UP_DT DESC)
                                                               AS MAX_SEQ_NO



-- Example 2

/* 
ROW_NUMBER( ) gives a running serial number to a partition of records. 
It is very useful in reporting, especially in places where different partitions have their own serial numbers. 
In Query-5, the function ROW_NUMBER( ) is used to give separate sets of running serial to employees of departments 10 and 20 based on their HIREDATE.
*/

SELECT empno, deptno, hiredate,
ROW_NUMBER( ) OVER (PARTITION BY
deptno ORDER BY hiredate
NULLS LAST) SRLNO
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, SRLNO;

EMPNO  DEPTNO HIREDATE       SRLNO
------ ------- --------- ----------
  7782      10 09-JUN-81          1
  7839      10 17-NOV-81          2
  7934      10 23-JAN-82          3
  7369      20 17-DEC-80          1
  7566      20 02-APR-81          2
  7902      20 03-DEC-81          3
  7788      20 09-DEC-82          4
  7876      20 12-JAN-83          5

8 rows selected.

source

Find Physical Files in SQL Server

select physical_name from sys.master_files

source

Search and Replace String in an SQL Server nText Field

UPDATE TABLENAME SET FIELDNAME = cast(replace(cast(FIELDNAME as nvarchar(max)),'find-this','replace-this') as ntext)
WHERE Content like '%find-this%'

source

Display Missing Indexes of the Current DB ordered by Index Advantage

-- Missing Indexes current database by Index Advantage
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage], 
migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY index_advantage DESC;

source

Change User Password

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='username';
FLUSH PRIVILEGES;

source

Memory Clerks & used vas

Select [type], memory_node_id, single_pages_kb, multi_pages_kb, virtual_memory_reserved_kb, virtual_memory_committed_kb, awe_allocated_kb
From sys.dm_os_memory_clerks
Order by virtual_memory_reserved_kb DESC;

source

TSQL – Find All Empty Columns in a Database

-- Returns a list of all columns in current database
-- where the column's value is null for all records.
declare @tempTable table
(
    TableSchema nvarchar(256),
    TableName nvarchar(256),
    ColumnName sysname,
    NotNullCnt bigint
);

declare @sql nvarchar(4000);
declare @tableSchema nvarchar(256);
declare @tableName nvarchar(256);
declare @columnName sysname;
declare @cnt bigint;

declare columnCursor cursor for
    select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
    where IS_NULLABLE = 'YES';

open columnCursor;

fetch next from columnCursor into @tableSchema, @tableName, @columnName;

while @@FETCH_STATUS = 0
begin
    -- use dynamic sql to get count of records where column is not null
    set @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName +
        '] where [' + @columnName + '] is not null';
    -- print @sql; --uncomment for debugging
    exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

    insert into @tempTable select @tableSchema, @tableName, @columnName, @cnt;

    fetch next from columnCursor into @tableSchema, @tableName, @columnName;
end

close columnCursor;
deallocate columnCursor;

select * from @tempTable where NotNullCnt = 0;

source