Skip to main content

SQL Queries that i find very handy


  • The following query determines missing product ids from a sequence of product ids

select l.Productid + 1 as start
from product as l
  left outer join product as r on l.Productid + 1 = r.Productid

where r.Productid is null;

  • The following query gives the recently modified stored procedures

select name,create_date,modify_date
from sys.procedures
order by modify_date desc

  • The following query gives me the C# class entity representation of a database table


DECLARE @TableName VARCHAR(MAX) = 'NewsItem' -- Replace 'NewsItem' with your table name
DECLARE @TableSchema VARCHAR(MAX) = 'dbo' -- Replace 'Markets' with your schema name
DECLARE @result varchar(max) = ''

SET @result = @result + 'using System;' + CHAR(13) + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
    SET @result = @result + 'namespace ' + @TableSchema  + CHAR(13) + '{' + CHAR(13)
END

SET @result = @result + 'public class ' + @TableName + CHAR(13) + '{' + CHAR(13)

SET @result = @result + '#region Instance Properties' + CHAR(13) 

SELECT @result = @result + CHAR(13)
    + ' public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13)
FROM
(
    SELECT  c.COLUMN_NAME   AS ColumnName
        , CASE c.DATA_TYPE  
            WHEN 'bigint' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int64?' ELSE 'Int64' END
            WHEN 'binary' THEN 'Byte[]'
            WHEN 'bit' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Boolean?' ELSE 'Boolean' END           
            WHEN 'char' THEN 'String'
            WHEN 'date' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       
            WHEN 'datetime' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       
            WHEN 'datetime2' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                       
            WHEN 'datetimeoffset' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END                                   
            WHEN 'decimal' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                   
            WHEN 'float' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Single?' ELSE 'Single' END                                   
            WHEN 'image' THEN 'Byte[]'
            WHEN 'int' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int32?' ELSE 'Int32' END
            WHEN 'money' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                               
            WHEN 'nchar' THEN 'String'
            WHEN 'ntext' THEN 'String'
            WHEN 'numeric' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                           
            WHEN 'nvarchar' THEN 'String'
            WHEN 'real' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Double?' ELSE 'Double' END                                                                       
            WHEN 'smalldatetime' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                   
            WHEN 'smallint' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int16?' ELSE 'Int16'END           
            WHEN 'smallmoney' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                                       
            WHEN 'text' THEN 'String'
            WHEN 'time' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END                                                                                   
            WHEN 'timestamp' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                   
            WHEN 'tinyint' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Byte?' ELSE 'Byte' END                                               
            WHEN 'uniqueidentifier' THEN 'Guid'
            WHEN 'varbinary' THEN 'Byte[]'
            WHEN 'varchar' THEN 'String'
            ELSE 'Object'
        END AS ColumnType
        , c.ORDINAL_POSITION
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA 
) t
ORDER BY t.ORDINAL_POSITION

SET @result = @result + CHAR(13) + '#endregion Instance Properties' + CHAR(13) 

SET @result = @result  + '}' + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
    SET @result = @result + CHAR(13) + '}'
END

PRINT @result

Comments

Popular posts from this blog

A simple customization in MPOS (Blank Operation) with AX7

Hi All, I recently had to add a Blank Operation to Modern POS (MPOS) to open a url from MPOS. Blank Operation as you may already be aware enable you to extend Microsoft Dynamics Retail for POS by adding custom logic that can be triggered from the Retail POS Register buttons. The way to implement Blank Operations in MPOS  is different from Enterprise POS as MPOS is a modern app as compared to EPOS which is a windows forms based app. So lets explore a very simple customization i.e. we want to open a URL on triggering a button from MPOS. 1. We would need to start with AX to add a button to the layout of MPOS. If you do not want to disturb the standard layouts its better to copy one of the existing layouts and then modify it using the designer. Please note the designer only opens in Internet Explorer so it will save you time by not trying to open it in other browsers e.g Chrome 2. Next we need to add this layout to the Store where we intend to use it, in

Hyper-V VM 100% disk utilization

I was facing a persistent issue of 100% disk utilization on starting any VM in my Hyper-V. The below link helped me to resolve it https://social.technet.microsoft.com/Forums/office/en-US/2b184f22-ea8c-4bc3-9f64-8b46eeaeef61/hyperv-host-disk-usage-at-100?forum=w8itprovirt