Write query to take a backup of all databases in a SQL Server

Use the following query to take a backup of all databases in a SQL SERVER

use master

go

IF  (OBJECT_ID('tempdb..#UserDbS')IS NOT NULL) DROP TABLE #UserDbS

SELECT NAME INTO #UserDbS FROM SYS.sysdatabases WHERE sid <> '0x01' AND NAME NOT IN ('master','tempdb','model','msdb')

DECLARE @DBBACKUPPATH NVARCHAR(MAX) = 'Z:\SQLServer\Backups\',

@BackedDate NVARCHAR(50) =  '_'+REPLACE(CONVERT(CHAR(10), GETDATE(), 103), '/', '') 

--SELECT * FROM #UserDbS

WHILE EXISTS (SELECT * FROM #UserDbS)

BEGIN

DECLARE @SQLCMD NVARCHAR(MAX),@DbName SYSNAME

SELECT TOP 1 @DbName = NAME FROM #UserDbS

SET @SQLCMD = 'BACKUP DATABASE ['+@DbName+'] TO  DISK = N'''+@DBBACKUPPATH+@DbName+@BackedDate+'.bak'' WITH NOFORMAT, NOINIT,  NAME = N'''+@DbName+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

PRINT @SQLCMD

EXEC (@SQLCMD)

DELETE #UserDbS WHERE name = @DbName

END

Write a query to print all prime numbers less than or equal to 1000

Write a query to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the ampersand (&) character as your separator (instead of a space).

For example, the output for all prime numbers <=10  would be:

2&3&5&7

Use below T-SQL query to achieve the above scenario

SELECT DISTINCT 
REPLACE(STUFF(REPLACE((SELECT '~' + CAST(ST1.Number  AS VARCHAR)  AS 'data()' 
            FROM (SELECT DISTINCT Number 
        FROM MASTER..SPT_VALUES AS a 
        WHERE 
        A.Number > 1 AND A.Number <= 1000 AND 
        NOT EXISTS 
        ( 
          SELECT 1 FROM MASTER..SPT_VALUES AS b WHERE b.Number > 1 
        AND  b.Number < a.Number 
        AND  a.Number % b.Number = 0)) ST1 
            ORDER BY ST1.Number 
FOR XML PATH('')),'~','&'), 1, 1, ''),' ','') as [PRIME_NUMBERS] 
FROM MASTER..SPT_VALUES ST2 WHERE ST2.Number > 1 AND ST2.Number <= 1000

Please see the below output of the above query:
2&3&5&7&11&13&17&19&23&29&31&37&41&43&47&53&59&61&67&71&73&79&83&89&97&101&103&107&109&113&127&131&137&139&149&151&157&163&167&173&179&181&191&193&197&199&211&223&227&229&233&239&241&251&257&263&269&271&277&281&283&293&307&311&313&317&331&337&347&349&353&359&367&373&379&383&389&397&401&409&419&421&431&433&439&443&449&457&461&463&467&479&487&491&499&503&509&521&523&541&547&557&563&569&571&577&587&593&599&601&607&613&617&619&631&641&643&647&653&659&661&673&677&683&691&701&709&719&727&733&739&743&751&757&761&769&773&787&797&809&811&821&823&827&829&839&853&857&859&863&877&881&883&887&907&911&919&929&937&941&947&953&967&971&977&983&991&997


You can use below query either to achieve the same result without using SPT_VALUES view of master database.

DECLARE @Nums TABLE(Number INT)

DECLARE @NUM INT =1

WHILE (@NUM <= 1000)
BEGIN
INSERT @Nums SELECT @NUM
SET @NUM = @NUM + 1
END

SELECT DISTINCT
REPLACE(STUFF(REPLACE((SELECT '~' + CAST(ST1.Number  AS VARCHAR)  AS 'data()'
            FROM (SELECT DISTINCT Number
        FROM @Nums AS a
        WHERE
        A.Number > 1 AND A.Number <= 1000 AND
        NOT EXISTS
        (
          SELECT 1 FROM @Nums AS b WHERE b.Number > 1
        AND  b.Number < a.Number
        AND  a.Number % b.Number = 0)) ST1
            ORDER BY ST1.Number
FOR XML PATH('')),'~','&'), 1, 1, ''),' ','') as [PRIME_NUMBERS]
FROM @Nums ST2 WHERE ST2.Number > 1 AND ST2.Number <= 1000

you can use below query too.


DECLARE @PRIME VARCHAR(max) =''; 

WITH nums 
     AS (SELECT 0 AS NUM 
         UNION ALL 
         SELECT num + 1 
         FROM   nums 
         WHERE  num < 9) 
SELECT @PRIME = @PRIME + Cast(A.num AS VARCHAR(3)) + '&' 
FROM   (SELECT A.num + B.num * 10 + C.num * 100 AS NUM 
        FROM   nums A 
               CROSS JOIN nums B 
               CROSS JOIN nums C) A 
       LEFT JOIN (SELECT A.num + B.num * 10 + C.num * 100 AS NUM 
                  FROM   nums A 
                         CROSS JOIN nums B 
                         CROSS JOIN nums C) B 
              ON Sqrt(A.num) >= B.num 
                 AND B.num > 1 
WHERE  A.num > 1 
GROUP  BY A.num 
HAVING Sum(CASE 
             WHEN A.num % B.num = 0 THEN 1 
             ELSE 0 
           END) = 0 
ORDER  BY A.num 

PRINT Substring(@PRIME, 0, Len(@PRIME)) 

How to get DROP AND CREATE script for all TABLE(s) with DEFAULT CONSTRAINTS USING simple SELECT in SQL SERVER

Below T-SQL QUERY helps to get DROP and CREATE script for all TABLE(s) with DEFAULT CONSTRAINTS USING simple SELECT in SQL SERVER. Feel free to use it if needed,  Kindly let me know in case if you got a better and easy way of doing it (do not say 😊 that it can be done using SSMS, right-click on the database-->Tasks-->Generate Scripts...).

SELECT DISTINCT 'IF EXISTS (SELECT * FROM AdventureWorksDW2017.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '''+ST.TABLE_SCHEMA+''' AND TABLE_NAME = '''+ST.TABLE_NAME+''' AND TABLE_CATALOG =''AdventureWorksDW2017'') DROP TABLE AdventureWorksDW2017.'+QUOTENAME(ST.TABLE_SCHEMA)+'.'+QUOTENAME(ST.TABLE_NAME)+ CHAR(13)+'CREATE TABLE AdventureWorksDW2017.'+QUOTENAME(ST.TABLE_SCHEMA)+'.'+QUOTENAME(ST.TABLE_NAME)+' ( ' +ISNULL(STUFF(REPLACE((SELECT '~~' + QUOTENAME(C.COLUMN_NAME) + ' ' +DATA_TYPE +CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '('+ CASE WHEN CHARACTER_MAXIMUM_LENGTH ='-1' THEN CAST('MAX' AS VARCHAR) ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END+')' ELSE ' ' END+ ' '+CASE WHEN  COLUMNPROPERTY(OBJECT_ID(QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME)), C.COLUMN_NAME, 'ISIDENTITY') =1 THEN    'IDENTITY(' +     CAST(IDENT_SEED(ST.TABLE_NAME) AS VARCHAR) + ',' +     CAST(IDENT_INCR(ST.TABLE_NAME) AS VARCHAR) + ')'   ELSE ''   END + ' ' +( CASE WHEN C.IS_NULLABLE = 'NO' THEN 'NOT ' ELSE '' END ) + 'NULL '+ CASE WHEN C.COLUMN_DEFAULT IS NOT NULL THEN ' CONSTRAINT '+DEF_CONS.CONSTRAINT_NAME+' DEFAULT ' +C.COLUMN_DEFAULT ELSE '' END+ ' 'AS 'data()' FROM INFORMATION_SCHEMA.TABLES T INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_SCHEMA=C.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAMELEFT JOIN (SELECT SDC.name CONSTRAINT_NAME,SCH.name AS TABLE_SCHEMA   ,ST.name TABLE_NAME,SC.name COLUMN_NAME FROM SYS.COLUMNS  AS SC INNER JOIN SYS.TABLES AS ST ON ST.OBJECT_ID = SC.OBJECT_ID INNER JOIN SYS.SCHEMAS AS SCH ON SCH.SCHEMA_ID = ST.SCHEMA_ID INNER JOIN SYS.default_constraints AS SDC ON SDC.parent_object_id = SC.object_id AND SDC.parent_column_id = SC.column_id  WHERE  SDC.name IS NOT NULL ) DEF_CONS ON DEF_CONS.TABLE_SCHEMA =C.TABLE_SCHEMA AND DEF_CONS.TABLE_NAME=C.TABLE_NAME  AND DEF_CONS.COLUMN_NAME = C.COLUMN_NAME   WHERE T.TABLE_NAME = SC.TABLE_NAME AND T.TABLE_SCHEMA = SC.TABLE_SCHEMA FOR XML PATH('')),'~~',', '), 1, 2, ''),'') + ')' --as [TABLE_CREATE_SCRIPT]  FROM INFORMATION_SCHEMA.TABLES ST INNER JOIN INFORMATION_SCHEMA.COLUMNS SC ON ST.TABLE_SCHEMA=SC.TABLE_SCHEMA AND ST.TABLE_NAME = SC.TABLE_NAME WHERE ST.TABLE_TYPE ='BASE TABLE' AND ST.TABLE_NAME <> 'sysdiagrams'

I have executed it under AdventureWorks2017 sample database. Below is the output

The above script would only provide the script to drop and create the table. If the table is foreign key referenced. You will have to drop the foreign key and referenced keys before dropping the table. Below script can be used to drop and create primary and unique keys.

USE AdventureWorksDW2017

GO

SELECT TABLE_SCHEMA,TABLE_NAME,INDEX_NAME CONSTRAINT_NAME,'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE CONSTRAINT_NAME = '''+INDEX_NAME+''' AND TABLE_SCHEMA = '''+TABLE_SCHEMA+''' AND TABLE_NAME = '''+TABLE_NAME+''' AND TABLE_CATALOG =''AdventureWorksDW2017'') ALTER TABLE '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+' DROP CONSTRAINT'+ QUOTENAME(INDEX_NAME) + CHAR(13)+'ALTER TABLE '+  QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+ ' ADD CONSTRAINT ' +  QUOTENAME(INDEX_NAME) + IS_UNIQUE_CONSTRAINT + IS_PRIMARY_KEY + INDEX_TYPE_DESC +  '('+[INDEX_COLUMNS]+' ) '-- +case when len([INCLUDED_COLUMNS])>0 then CHAR(13) +'INCLUDE (' + [INCLUDED_COLUMNS]+ ')' else '' end + CHAR(13)+'WITH (' + INDEXOPTIONS+ ') ON ' + QUOTENAME(FILEGROUPNAME) + ';'    AS SQLCMD --INTO ##PRIMARYKEY_CONSTRAINTS   FROM (SELECT DISTINCT SCHEMA_NAME(ST.SCHEMA_ID) TABLE_SCHEMA, ST.NAME TABLE_NAME, SIX.NAME INDEX_NAME,CASE WHEN SIX.IS_UNIQUE = 1 THEN ' UNIQUE ' ELSE '' END AS IS_UNIQUE,CASE WHEN SIX.IS_UNIQUE_CONSTRAINT = 1 THEN ' UNIQUE ' ELSE '' END AS IS_UNIQUE_CONSTRAINT,CASE WHEN SIX.IS_PRIMARY_KEY = 1 THEN ' PRIMARY KEY ' ELSE '' END AS IS_PRIMARY_KEY, SIX.TYPE_DESC COLLATE DATABASE_DEFAULT  INDEX_TYPE_DESC,  CASE  WHEN SIX.IS_PADDED=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END  + CASE WHEN SIX.ALLOW_PAGE_LOCKS=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END  + CASE WHEN SIX.ALLOW_ROW_LOCKS=1 THEN  'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END  + CASE WHEN INDEXPROPERTY(ST.OBJECT_ID, SIX.NAME, 'ISSTATISTICS') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END  + CASE WHEN SIX.IGNORE_DUP_KEY=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF ' END  AS INDEXOPTIONS,FILEGROUP_NAME(SIX.DATA_SPACE_ID) FILEGROUPNAME  ,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME)+ ' '+ CASE WHEN IXC.IS_DESCENDING_KEY =1 THEN 'DESC' ELSE 'ASC' END  AS 'data()'  FROM SYS.TABLES TJOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_IDJOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_IDJOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID  WHERE IX.TYPE>0 AND (SIX.IS_PRIMARY_KEY=1  OR SIX.IS_UNIQUE_CONSTRAINT=1) AND IXC.IS_INCLUDED_COLUMN <> 1AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAMEFOR XML PATH('')),'~~',', '), 1, 2, '') as [INDEX_COLUMNS],ISNULL(STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME) AS 'data()' FROM SYS.TABLES TJOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_IDJOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_IDJOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID  WHERE IX.TYPE>0 AND (SIX.IS_PRIMARY_KEY=1  OR SIX.IS_UNIQUE_CONSTRAINT=1) AND IXC.IS_INCLUDED_COLUMN = 1AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAMEFOR XML PATH('')),'~~',', '), 1, 2, ''),'') as [INCLUDED_COLUMNS]  FROM SYS.TABLES ST INNER JOIN SYS.INDEXES SIX ON ST.OBJECT_ID=SIX.OBJECT_ID  WHERE SIX.TYPE>0 AND (  SIX.IS_PRIMARY_KEY=1  OR SIX.IS_UNIQUE_CONSTRAINT=1)  AND ST.IS_MS_SHIPPED=0 AND ST.NAME<>'SYSDIAGRAMS'  ) A

I have executed it under AdventureWorks2017 sample database. Below is the output

Below script can be used to drop and create Indexes.

USE AdventureWorksDW2017

GO

SELECT INDEX_NAME,'IF EXISTS (SELECT * FROM SYS.INDEXES WHERE NAME = '''+INDEX_NAME+''') DROP INDEX '+QUOTENAME(INDEX_NAME)+' ON  ' + QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+' CREATE '+ IS_UNIQUE  +INDEX_TYPE_DESC + ' INDEX ' +QUOTENAME(INDEX_NAME)+' ON ' + QUOTENAME(TABLE_SCHEMA) +'.'+ QUOTENAME(TABLE_NAME)+ '('+[INDEX_COLUMNS]+' ) '+  case when [INCLUDED_COLUMNS]<>'' then CHAR(13) +'INCLUDE (' + [INCLUDED_COLUMNS]+ ')' else '' end + CHAR(13)+'WITH (' + INDEXOPTIONS+ ') ON ' + QUOTENAME(FILEGROUPNAME) + ';' SQLCMD  FROM (SELECT DISTINCT SCHEMA_NAME(ST.SCHEMA_ID) TABLE_SCHEMA, ST.NAME TABLE_NAME, SIX.NAME INDEX_NAME,CASE WHEN SIX.IS_UNIQUE = 1 THEN ' UNIQUE ' ELSE '' END AS IS_UNIQUE, SIX.TYPE_DESC COLLATE DATABASE_DEFAULT  INDEX_TYPE_DESC,  CASE  WHEN SIX.IS_PADDED=1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END  + CASE WHEN SIX.ALLOW_PAGE_LOCKS=1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END  + CASE WHEN SIX.ALLOW_ROW_LOCKS=1 THEN  'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END  + CASE WHEN INDEXPROPERTY(ST.OBJECT_ID, SIX.NAME, 'ISSTATISTICS') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END  + CASE WHEN SIX.IGNORE_DUP_KEY=1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF ' END  AS INDEXOPTIONS,FILEGROUP_NAME(SIX.DATA_SPACE_ID) FILEGROUPNAME  ,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME)+ ' '+ CASE WHEN IXC.IS_DESCENDING_KEY =1 THEN 'DESC' ELSE 'ASC' END  AS 'data()'FROM SYS.TABLES TJOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_IDJOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_IDJOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID  WHERE IX.TYPE>0 AND (IX.IS_PRIMARY_KEY=0 AND IX.IS_UNIQUE_CONSTRAINT=0) AND IXC.IS_INCLUDED_COLUMN <> 1AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAMEFOR XML PATH('')),'~~',', '), 1, 2, '') as [INDEX_COLUMNS],ISNULL(STUFF(REPLACE((SELECT '~~' + QUOTENAME(COL.NAME) AS 'data()' FROM SYS.TABLES TJOIN SYS.INDEXES IX ON T.OBJECT_ID=IX.OBJECT_IDJOIN SYS.INDEX_COLUMNS IXC ON IX.OBJECT_ID=IXC.OBJECT_ID AND IX.INDEX_ID= IXC.INDEX_IDJOIN SYS.COLUMNS COL ON IXC.OBJECT_ID =COL.OBJECT_ID  AND IXC.COLUMN_ID=COL.COLUMN_ID  WHERE IX.TYPE>0 AND (IX.IS_PRIMARY_KEY=0 AND IX.IS_UNIQUE_CONSTRAINT=0) AND IXC.IS_INCLUDED_COLUMN = 1AND SCHEMA_NAME(T.SCHEMA_ID) = SCHEMA_NAME(ST.SCHEMA_ID) AND T.NAME = ST.NAME AND IX.NAME = SIX.NAMEFOR XML PATH('')),'~~',', '), 1, 2, ''),'') as [INCLUDED_COLUMNS]  FROM SYS.TABLES ST INNER JOIN SYS.INDEXES SIX ON ST.OBJECT_ID=SIX.OBJECT_ID  WHERE SIX.TYPE>0 AND ( SIX.IS_PRIMARY_KEY=0  AND SIX.IS_UNIQUE_CONSTRAINT=0)  AND ST.IS_MS_SHIPPED=0 AND ST.NAME<>'SYSDIAGRAMS'  ) A

Please feel free to use the scripts when in need.