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.