Samarth Diamond - How to convert MSSQL Table into Frappe Doctype easily and Fast

Please go through following video for Understanding concept:

https://youtube.com/live/93urXHf1jsc?feature=share

Query Used in Videos:

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UnCommitted;

declare @DBname as nvarchar(10) ='Polish';

declare @TName as nvarchar(50) = '';

declare @sql nvarchar(max);

set @sql = '

SELECT ROW_NUMBER() OVER(ORDER BY table_name) AS idx

, lower(column_name) as fieldname,column_name as label

,case when Data_Type = ''date'' then ''Date''

when Data_Type = ''bit'' then ''Check''

when Data_Type in (''int'',''smallint'',''tinyint'',''bigint'') then ''Int''

when Data_Type in (''float'',''real'',''numeric'',''decimal'') then ''Float''

when Data_Type in (''datetime2'',''datetime'',''smalldatetime'') then ''Datetime''

else ''Data'' end as fieldtype

, ''DocField'' as doctype

-- ,*

FROM @DBname.INFORMATION_SCHEMA.COLUMNS

where Table_Name=''@TName''

FOR JSON PATH;

'

set @sql = replace(@sql, '@DBname', @DBname);

set @sql = replace(@sql, '@TName', @TName);

--Select @sql

exec sp_executesql @sql;

JSON Format :

{
    "docstatus": 0,
    "doctype": "DocType",
    "fields":
    [{
        "idx": 1,
        "fieldname": "purity_code",
        "label": "Purity_Code",
        "fieldtype": "Int",
        "doctype": "DocField"
    }, {
        "idx": 2,
        "fieldname": "purity_name",
        "label": "Purity_Name",
        "fieldtype": "Data",
        "doctype": "DocField"
    }
    ],
  "permissions": [{
            "docstatus": 0,
            "parentfield": "permissions",
            "parenttype": "DocType",
            "idx": 1,
            "permlevel": 0,
            "role": "System Manager",
            "read": 1,
            "write": 1,
            "create": 1,
            "submit": 0,
            "cancel": 0,
            "delete": 1,
            "amend": 0,
            "report": 1,
            "export": 1,
            "import": 0,
            "share": 1,
            "print": 1,
            "email": 1,
            "if_owner": 0,
            "select": 0,
            "set_user_permissions": 0,
            "doctype": "DocPerm"
        }
    ],
    "index_web_pages_for_search": 1,
    "is_submittable": 0,
    "is_tree": 0,
    "is_virtual": 0,
    "issingle": 0,
    "module": "Samarth",
    "name": "Polish_Purity_Mst",
    "naming_rule": "Expression",
    "autoname": "format:{#}",
    "title_field": "",
    "search_fields": "",
    "owner": "Administrator"
}