GDrive - Delete All records in Active Sheet

function DeleteDataInActiveSheet() {

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var start, end;

    start = 1;
    end = sheet.getLastRow();

    sheet.deleteRows(start, end)

}

GDrive - List Files and Folders Recursive

function getFolders() {
 //https://developers.google.com/apps-script/reference/drive/folder-iterator

      var iRow = 1;
      var iColumn = 1;
   
      var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      ss.getRange(iRow, iColumn).setValue("Folder");
      ss.getRange(iRow, iColumn + 1).setValue("FileName");
      ss.getRange(iRow, iColumn + 2).setValue("Count");

 var folders = DriveApp.getFolders();
 while (folders.hasNext()) {
      var folder = folders.next();
      var iCount = 0;

      //file iteration
      var dApp = DriveApp;
      var folderIter = dApp.getFoldersByName(folder.getName());
   
      var folder = folderIter.next();
      var filesIter = folder.getFiles();

     ss.getRange(iRow, iColumn).setValue(folder.getName());
     ss.getRange(iRow, iColumn + 1).setValue("NewFolder");
     ss.getRange(iRow, iColumn + 2).setValue(iCount);
    
    
      while (filesIter.hasNext()) {
        var file = filesIter.next();
        var fileName = file.getName();
     
       iRow = iRow + 1;
       iCount = iCount+1;
        //Logger.log(folder.getName() + " " + fileName );
        ss.getRange(iRow, iColumn).setValue(folder.getName());
        ss.getRange(iRow, iColumn + 1).setValue(fileName);
             ss.getRange(iRow, iColumn + 2).setValue(iCount);
      }
 }
}

split a text based on one character

Requirement:

- In excel need to get left / right values based on one character.  Here we use "="

Text	LeftFormula	LeftValue	RightFormula	RightValue
abcd=123	=LEFT(A2,FIND("=",A2,1)-1)	abcd	=RIGHT(A2,LEN(A2)-FIND("=",A2))	123
a=1	=LEFT(A3,FIND("=",A3,1)-1)	a	=RIGHT(A3,LEN(A3)-FIND("=",A3))	1
b=2	=LEFT(A4,FIND("=",A4,1)-1)	b	=RIGHT(A4,LEN(A4)-FIND("=",A4))	2
c=3	=LEFT(A5,FIND("=",A5,1)-1)	c	=RIGHT(A5,LEN(A5)-FIND("=",A5))	3
abcde=12345	=LEFT(A6,FIND("=",A6,1)-1)	abcde	=RIGHT(A6,LEN(A6)-FIND("=",A6))	12345

SQL Server - Sequence # = Year + Julian Date + Sequence Number

/*
Requirement:
Application number should be 10 char
Application Number first firve char year + julian date, rest of 5 number will be sequence number

*/


DECLARE @APPLN_NUM as int;
 -- This will be maximum number in table - to be selected
SET @APPLN_NUM = 1822500001  -- if you comment this line, first number will be generated



IF (@APPLN_NUM) is null
        BEGIN
              SELECT CONVERT(VARCHAR,right(DATEPART(yy, getdate()),2)) +  CONVERT(VARCHAR,DATEPART(dy, getdate())) + '00001';
       END
ELSE
        BEGIN
              SELECT CONVERT(VARCHAR,right(DATEPART(yy, getdate()),2)) +  CONVERT(VARCHAR,DATEPART(dy, getdate())) + right(@APPLN_NUM,5) + 1
        END

SQL Server 2017 DB Object Count From Sysobjects

Reference  https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql?view=sql-server-2017


Select --Name,
ObjType =
CASE xtype
WHEN 'AF' THEN 'Aggregate function (CLR)'
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'IT' THEN 'Internal table'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'PC' THEN 'Assembly (CLR) stored-procedure'
WHEN 'PK' THEN 'PRIMARY KEY constraint (type is K)'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'SN' THEN 'Synonym'
WHEN 'SQ' THEN 'Service queue'
WHEN 'TA' THEN 'Assembly (CLR) DML trigger'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'SQL DML Trigger'
WHEN 'TT' THEN 'Table type'
WHEN 'U' THEN 'User table'
WHEN 'UQ' THEN 'UNIQUE constraint (type is K)'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
WHEN 'SO' THEN 'Sequences' --Missing in microsoft article
WHEN 'SP' THEN 'Security Policy'--Missing in microsoft article
ELSE 'NotKnown -> ' + xtype
END, count(xtype) as Cnt
 from sysobjects group by xtype
 order by Cnt

SQL Designer : Saving Changes in not permitted. The changes.... Issue

 

Menu >> Tools >> Options >> Designers >> Uncheck “Prevent Saving changes that require table re-creation”.

 

 

Thanks to

https://blog.sqlauthority.com/2009/05/18/sql-server-fix-management-studio-error-saving-changes-in-not-permitted-the-changes-you-have-made-require-the-following-tables-to-be-dropped-and-re-created-you-have-either-made-changes-to-a-tab/

Error : Saving Changes in not permitted...

Management Studio Error : Saving Changes in not permitted. The changes you have made require the following tables to be dropped and re-created.

Menu >> Tools >> Options >> Designers >> Uncheck "Prevent Saving changes that require table re-creation".

Thanks to : https://blog.sqlauthority.com/2009/05/18/sql-server-fix-management-studio-error-saving-changes-in-not-permitted-the-changes-you-have-made-require-the-following-tables-to-be-dropped-and-re-created-you-have-either-made-changes-to-a-tab/