Solved

generate a script by object type TABLE, FOREIGN_KEY, TRIGGER, CHECKCONSTRAINT

Posted on 2016-08-04
6
37 Views
Last Modified: 2016-08-12
Hello,

I search to modify this script to generate a script by object type TABLE, FOREIGN_KEY, TRIGGER, CHECKCONSTRAINT :

param (
  [string] $Directory="d:\ddl\",
  [string] $SQLSERVER="SQL\TEST",
  [string] $Database="DBTEST",
  [string] $Action="obj"
)
$set = new-object system.data.dataset
(new-object System.Data.SqlClient.SqlDataAdapter (@"
    truncate table [ADM].[dbo].TESTFR_ddl_configuration_wk
    insert into [ADM].[dbo].TESTFR_ddl_configuration_wk
    SELECT distinct schema_name=schema_name(o.schema_id),  ddl.[object_name], ddl.new_fg_name 
	FROM [ADM].[dbo].[TESTFR_ddl_configuration] ddl
	INNER JOIN sys.all_objects o
	on ddl.[object_name]= o.[name]
	INNER JOIN sys.all_columns c
	ON c.object_id = o.object_id
	INNER JOIN  sys.types t
	on c.system_type_id = t.system_type_id 

    WHERE ddl.new_fg_name <> 'NULL' and  ddl.new_fg_name <> ''
    and  c.system_type_id IN (35,34,241,99)
	AND o.[name] NOT LIKE 'sys%'
	AND o.[name] <> 'dtproperties'
	AND o.[type] = 'U'
	AND (o.schema_id = schema_id('$Schema') or '$Schema' = '') 
	select * from [ADM].[dbo].TESTFR_ddl_configuration_wk
"@, "server=$SQLSERVER; Database=$Database; Integrated Security=sspi")).Fill($set) | out-null

$set.Tables[0] | % {
  echo "$($_.schema_name).$($_.object_name)"
  $scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SQLSERVER)
  $scrp.Options.DriPrimaryKey=$true
  $scrp.Options.Triggers=$true
  $scrp.Options.DriForeignKeys=$true
  $scrp.Options.DriChecks=$true
  $scrp.Options.AppendToFile = $true 
  $scrp.Options.FileName = "D:\scripts\TESTFR_tbl_ddl.sql"
  $scrp.Script($db.Tables.Item($_.object_name,$_.schema_name))
 
 }

Open in new window


 How can I do it, please ?
 
 Thanks
 
 Regards
0
Comment
Question by:bibi92
  • 3
  • 3
6 Comments
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
What exactly are you trying to achieve?
0
 

Author Comment

by:bibi92
Comment Utility
Hello,

I try to generate a script by object type :

new.Create.Tables.sql
new.Create.Triggers.sql
new.Create.ForeignKeys.sql
new.Create.CheckConstraints.sql

Thanks

Regards
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
I can give you a query for that. It will return all the objects in a database along with the their code, when it was created. last time modified, etc:
	select 
		@@SERVERNAME as [server],
		db_name() [database],
		[parent_object_id],
		object_name([parent_object_id]) as parent_object_name,
		[object_id],
		name as [object_name],
		[type] as [object_type], 
		type_desc,
		create_date as object_create_date, 
		modify_date as object_modify_date,
		object_code,
		isnull(len(object_code),0) as object_code_length,
		master.sys.fn_repl_hash_binary(convert(varbinary(max),isnull(object_code,''))) as object_code_hash
		--into zb_dba_dbcode.dbo.dbcode_objects_code_repository
	from
		(
			select distinct
				ob.[object_id],
				ob.parent_object_id,
				name,
				ob.[type], 
				case ob.[type]
					when 'AF' Then 'Aggregate function (CLR)'
					when 'C' Then 'CHECK constraint'
					when 'D' Then 'DEFAULT (constraint or stand-alone)'
					when 'F' Then 'FOREIGN KEY constraint'
					when 'FN' Then 'SQL scalar function'
					when 'FS' Then 'Assembly (CLR) scalar-function'
					when 'FT' Then 'Assembly (CLR) table-valued function'
					when 'IF' Then 'SQL inline table-valued function'
					when 'IT' Then 'Internal table'
					when 'P' Then 'SQL Stored Procedure'
					when 'PC' Then 'Assembly (CLR) stored-procedure'
					when 'PG' Then 'Plan guide'
					when 'PK' Then 'PRIMARY KEY constraint'
					when 'R' Then 'Rule (old-style, stand-alone)'
					when 'RF' Then 'Replication-filter-procedure'
					when 'S' Then 'System base table'
					when 'SN' Then 'Synonym'
					when 'SO' Then 'Sequence object'
					when 'SQ' Then 'Service queue'
					when 'TA' Then 'Assembly (CLR) DML trigger'
					when 'TF' Then 'SQL table-valued-function'
					when 'TR' Then 'SQL DML trigger'
					when 'TT' Then 'Table type'
					when 'U' Then 'Table (user-defined)'
					when 'UQ' Then 'UNIQUE constraint'
					when 'V' Then 'View'
					when 'X' Then 'Extended stored procedure'
					else null
				end as type_desc,
				create_date, 
				modify_date ,
				case 
					when ob.[type]='U' then tbls.table_code
					when ob.[type] IN ('IK','PK','UQ') then idx.index_code
					when ob.[type] IN ('F') then fks.fk_code
					when ob.[type] IN ('TT') then tts.tt_code
					when ob.[type] IN ('D') then 
						ISNULL((select [name] from sys.columns where object_id=ob.parent_object_id 
						and column_id = (select parent_column_id from sys.default_constraints where object_id=ob.object_id))+' ','')+
							OBJECT_DEFINITION(ob.[object_id])
					else	OBJECT_DEFINITION(ob.[object_id])
				end												as object_code
			from
				sys.objects ob
				left join
				(
					select  
						so.[object_id],
						'U' as [type],
						'CREATE TABLE [' + so.name + '] (' + replace(rtrim(o.list)+'@#$',',@#$','') + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE char(10)+'ALTER TABLE ' + so.Name + char(10)+' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END+char(10)+char(10)+char(10)+char(10) table_code
					from    
						sys.objects so
						cross apply
						(
							SELECT 
								char(10)+'  ['+column_name+'] ' + 
								data_type + 
								case 
									when data_type in ('sql_variant','text','ntext','geometry','geography','xml') then ''
									when data_type='decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
									else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') 
								end + ' ' +
								case 
									when exists (select object_id from sys.columns
													where object_name(object_id)=so.name
													and name=column_name
													and columnproperty(object_id,name,'IsIdentity') = 1) 
										then
										'IDENTITY(' + 
										cast(ident_seed(so.name) as varchar) + ',' + 
										cast(ident_incr(so.name) as varchar) + ')'
									else ''
								end + ' ' +
									(case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
									case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', ' 

								from 
								information_schema.columns 
							where 
								table_name = so.name
							order by ordinal_position
							FOR XML PATH('')
						) o (list)
						left join information_schema.table_constraints tc
							on  tc.Table_name       = so.Name
							AND tc.Constraint_Type  = 'PRIMARY KEY'
						cross apply
						(
							select 
								'[' + Column_Name + '], '
							FROM   
								information_schema.key_column_usage kcu
							WHERE  
								kcu.Constraint_Name = tc.Constraint_Name
							ORDER BY
								ORDINAL_POSITION
							FOR XML PATH('')
						) j (list)
					where
						[type] = 'U'
						--AND name NOT IN ('dtproperties')
						--and so.object_id=ob.object_id
				)  as tbls
					ON ob.[object_id]=tbls.[object_id]
					--AND tbls.[type]=ob.[type]
				left join
				(
					SELECT
						I.[object_id] as parent_table_id,
						T.name as TableName,
						I.name as IndexName,
						case 
							when I.is_primary_key = 1 then 'PK'
							when I.is_unique_constraint = 1 then 'UQ'
							--when I.is_primary_key = 0 then 
							else 'IX'
						end as [Type],
						CASE 
							WHEN I.is_primary_key = 1 OR I.is_unique_constraint = 1 
							THEN 

								char(10)+char(10)+'ALTER TABLE ' +
								Schema_name(T.Schema_id)+'.'+T.name  + 
									+' ADD CONSTRAINT ' + I.name +
								CASE 
									WHEN I.is_primary_key=1 THEN  ' PRIMARY KEY '  
									WHEN I.is_unique_constraint=1 THEN  ' UNIQUE '
									ELSE ''
								END + 
								I.type_desc COLLATE DATABASE_DEFAULT + ' ( ' +char(10)+char(9)+
								KeyColumns + ' )  '
						ELSE

								char(10)+char(10)+'CREATE ' +
								CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  + 
								I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +  
								I.name  + ' ON '  + 
								Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +char(10)+char(9)+
								KeyColumns + ' )  '
						END +
	
						ISNULL(char(10)+' INCLUDE ('+char(10)+char(9)+IncludedColumns+' ) ','') +
						ISNULL(' WHERE  '+I.Filter_definition,'') + char(10)+'WITH ( ' +
						CASE WHEN I.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', '  +
						CASE WHEN I.Fill_factor = 0 THEN '' ELSE 'FILLFACTOR = '+CONVERT(CHAR(5), I.Fill_factor) + ', '  END +
						-- default value
						'SORT_IN_TEMPDB = OFF'  + ', '  +
						CASE WHEN I.ignore_dup_key = 1 THEN 'IGNORE_DUP_KEY = ON' ELSE 'IGNORE_DUP_KEY = OFF' END + ', '  +
						CASE WHEN ST.no_recompute = 0 THEN 'STATISTICS_NORECOMPUTE = OFF' ELSE 'STATISTICS_NORECOMPUTE = ON' END + ', '  +
						-- default value 
						CASE WHEN NOT (I.is_primary_key=0 AND I.is_unique_constraint = 0) THEN 'DROP_EXISTING = OFF, '  ELSE '' END  +
						-- default value 
						'ONLINE = OFF'  + ', '  +
						CASE WHEN I.allow_row_locks = 1 THEN 'ALLOW_ROW_LOCKS = ON' ELSE 'ALLOW_ROW_LOCKS = OFF' END + ', '  +
						CASE WHEN I.allow_page_locks = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END  + ' ) ON [' +
						DS.name + ']' + char(10) +  char(10) + 'GO' + char(10)  
							AS index_code
					FROM 
						sys.indexes I  
						JOIN sys.tables T ON T.Object_id = I.Object_id   
						JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid  
						JOIN 
						(
							SELECT 
								IC2.object_id , 
								IC2.index_id , 
								STUFF(
									(
										SELECT ', ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END+char(10)+char(9)
										FROM 
											sys.index_columns IC1 
											JOIN Sys.columns C  
												ON C.object_id = IC1.object_id  
												AND C.column_id = IC1.column_id  
												AND IC1.is_included_column = 0 
										WHERE 
											IC1.object_id = IC2.object_id  
											AND IC1.index_id = IC2.index_id  
										GROUP BY 
											IC1.object_id,C.name,index_id 
										ORDER BY 
											MAX(IC1.key_ordinal) 
											FOR XML PATH('')
									), 1, 2, ''
								) as KeyColumns  
							FROM 
								sys.index_columns IC2  
							GROUP BY 
								IC2.object_id,
								IC2.index_id
						)tmp4  
							ON I.object_id = tmp4.object_id 
							AND I.Index_id = tmp4.index_id 
						JOIN sys.stats ST 
							ON ST.object_id = I.object_id AND ST.stats_id = I.index_id  
						JOIN sys.data_spaces DS 
							ON I.data_space_id=DS.data_space_id  
						JOIN sys.filegroups FG 
							ON I.data_space_id=FG.data_space_id  
						LEFT JOIN 
						(
								SELECT 
									IC2.object_id, 
									IC2.index_id,  
									STUFF(
										(
											SELECT 
												', ' + C.name +char(10)+char(9)
											FROM 
												sys.index_columns IC1  
												JOIN Sys.columns C   
													ON  C.object_id = IC1.object_id   
													AND C.column_id = IC1.column_id   
													AND IC1.is_included_column = 1  
											WHERE 
												IC1.object_id = IC2.object_id   
												AND IC1.index_id = IC2.index_id   
											GROUP BY 
												IC1.object_id,C.name,index_id  
											FOR XML PATH('')
										), 1, 2, '') IncludedColumns   
								FROM 
									sys.index_columns IC2   
							   GROUP BY 
								IC2.object_id,
								IC2.index_id
					   ) tmp2   
							ON 
								tmp2.object_id = I.object_id 
								AND tmp2.index_id = I.index_id 
								and tmp2.IncludedColumns is not null
					WHERE
						I.is_primary_key = 1 
						OR I.is_unique_constraint = 1
				) idx
					on idx.[parent_table_id]=ob.[parent_object_id]
					and idx.IndexName=ob.name
					and idx.Type=ob.type

				left join 
				(
					SELECT
						fk.[object_id],
						'F' as [type],
						--'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' DROP  CONSTRAINT' + ' ' + QUOTENAME(FK.name) AS [DropFKScript],
						'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' WITH CHECK ADD  CONSTRAINT '+ QUOTENAME(FK.name) + CHAR(13) + CHAR(10) +
						'FOREIGN KEY(' + ParentColumns.ColumnNames + ')' + CHAR(13) + CHAR(10) +
						'REFERENCES ' + QUOTENAME(SCHEMA_NAME(RT.[schema_id])) + '.' + QUOTENAME(RT.name) + ' (' + RefColumns.ColumnNames + ')' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) +
						'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(PT.[schema_id])) + '.' + QUOTENAME(PT.name) + ' CHECK CONSTRAINT ' + QUOTENAME(FK.name) + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
						AS fk_code
					FROM 
						sys.foreign_keys FK   
						INNER JOIN sys.tables PT 
							ON PT.[object_id] = FK.parent_object_id
						INNER JOIN 
						(
							   SELECT
									  C.parent_object_id AS [object_id],
									  C.referenced_object_id,
									  STUFF((SELECT ', ' + QUOTENAME(B.name)
											 FROM sys.foreign_key_columns A 
												   JOIN sys.columns B ON B.[object_id] = A.parent_object_id AND B.column_id = A.parent_column_id
												   WHERE C.parent_object_id = A.parent_object_id AND C.referenced_object_id = A.referenced_object_id
												   FOR XML PATH('')), 1, 2, '') AS ColumnNames
							   FROM sys.foreign_key_columns C
							   GROUP BY C.parent_object_id, C.referenced_object_id
						) AS ParentColumns 
							ON	ParentColumns.[object_id] = FK.parent_object_id 
							AND ParentColumns.referenced_object_id = FK.referenced_object_id
						INNER JOIN sys.tables RT 
							ON RT.[object_id] = FK.referenced_object_id
						INNER JOIN 
						(
						   SELECT
								  C.referenced_object_id AS [object_id],
								  C.parent_object_id,
								  STUFF((SELECT ', ' + QUOTENAME(B.name)
										 FROM sys.foreign_key_columns A 
											   JOIN sys.columns B ON B.[object_id] = A.referenced_object_id AND B.column_id = A.referenced_column_id
											   WHERE C.parent_object_id = A.parent_object_id AND C.referenced_object_id = A.referenced_object_id
											   FOR XML PATH('')), 1, 2, '') AS ColumnNames
						   FROM sys.foreign_key_columns C
						   GROUP BY C.referenced_object_id, C.parent_object_id
	
						) AS RefColumns 
							ON	RefColumns.[object_id] = FK.referenced_object_id 
							AND RefColumns.parent_object_id = FK.parent_object_id
					WHERE 
						PT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
						AND RT.name NOT IN ('dtproperties', 'sysdiagrams', '__RefactorLog')
				) as fks
					ON fks.[object_id]=ob.[object_id]
					and fks.[type]=ob.[type]

				left join 
				(
					select 
						ty.type_table_object_id as [object_id],
						'TT' as [type],
						'CREATE TYPE ['+schema_name(ty.[schema_id])+'].[' +ty.name+'] AS TABLE (' + replace(t.list+'#$%',',#$%','') +
						CASE WHEN q.pk_cols IS NULL THEN '' ELSE CHAR(10)+'PRIMARY KEY ' + ' (' + LEFT(q.pk_cols, Len(q.pk_cols)-1) + ')' +  CHAR(10) + ')' end   tt_code
					from
						sys.table_types ty
						cross apply
						(
							SELECT
								char(10)+char(9)+'['+column_name+'] ' + 
								data_type + 
								CASE 
									WHEN data_type not in ('decimal','numeric') and data_type NOT like '%char' and data_type NOT like '%time2'
										THEN ''
									WHEN data_type like '%time2'
										THEN '('+cast(NUMERIC_SCALE as varchar)+')'
									WHEN data_type in ('decimal','numeric')
										THEN '('+cast(NUMERIC_PRECISION as varchar)+','+cast(NUMERIC_SCALE as varchar)+')'
									WHEN data_type like '%char'  
										THEN '('+ISNULL(NULLIF(cast(CHARACTER_MAXIMUM_LENGTH as varchar),'-1'),'MAX')+')'
									WHEN CHARACTER_MAXIMUM_LENGTH<0 THEN ''
									ELSE '('+cast(CHARACTER_MAXIMUM_LENGTH as varchar)+')'
								END + ' ' +
								case 
									when is_identity = 1 
										then 
											'IDENTITY(' + 
											cast(tts.seed_value as varchar) + ',' + 
											cast(tts.increment_value as varchar) + ')'
									else	''
								end + ' ' +
									(case when tts.is_nullable = 0 then 'NOT ' else '' end ) + 'NULL ' + 
									ISNULL('DEFAULT '+tts.[definition],'') +','
								from 
								(
									select 
										tt.name AS table_Type, 
										c.name AS column_name,
										c.is_nullable,
										st.name AS data_type,
										c.max_length as character_maximum_length,
										c.[precision] as numeric_precision,
										c.scale as numeric_scale,
										d.[definition],
										c.column_id,
										c.is_identity,
										ic.seed_value,
										ic.increment_value
									from 
										sys.table_types tt
										inner join sys.columns c 
											on c.[object_id] = tt.type_table_object_id
										INNER JOIN sys.systypes AS ST  
											ON ST.xusertype = c.user_type_id
										left join sys.default_constraints d
											on d.[object_id]=c.default_object_id
										left join sys.identity_columns ic
											on ic.object_id=tt.type_table_object_id
											and ic.name=c.name
									where 
										tt.name=ty.name
								) tts
								order by column_id
								FOR XML PATH('')
						) t (list)

						cross apply
						(
							SELECT 
								clmns.name+','
							FROM
								sys.table_types AS tt
								INNER JOIN sys.indexes AS i 
									ON (i.object_id=tt.type_table_object_id)
								INNER JOIN sys.index_columns AS ic 
									ON (ic.index_id=i.index_id
									AND ic.object_id=i.object_id)
								INNER JOIN sys.columns AS clmns 
									ON clmns.object_id = ic.object_id 
									and clmns.column_id = ic.column_id
							WHERE
								((tt.name=ty.name ))
							ORDER BY
								ic.key_ordinal
							FOR XML PATH('')
						) q (pk_cols)
				) tts
					on tts.[object_id]=ob.[object_id]
			where 
				ob.[type] not in ('S','IT','SQ')
		) q2

	union

	select 
		[server],
		[database],
		parent_table_id,
		parent_table_name,
		index_id,
		index_name,
		cast(index_type as varchar(2)) index_type,
		index_type_desc,
		NULL as index_create_date,
		NULL as index_modify_date,
		index_code,
		len(index_code) as index_code_length,
		master.sys.fn_repl_hash_binary(convert(varbinary(max),index_code)) as index_code_hash
	from
		(
			SELECT
				CASE WHEN @@SERVERNAME LIKE 'DCSQL%' then 'DCSQLAOLSN' ELSE @@SERVERNAME END as [server],
				db_name() [database],
				I.[object_id] as parent_table_id,
				T.name as parent_table_name,
				I.index_id,
				I.name as index_name,
				I.[type] as index_type,
				case I.[type]
					when 0 then 'Heap'
					when 1 then 'Clustered'
					when 2 then 'Nonclustered'
					when 3 then 'XML'
					when 4 then 'Spatial'
					else 'IX'
				end + case when I.is_unique=1 then + ' UNIQUE' else '' end as index_type_desc,
				CASE 
					WHEN I.is_primary_key = 1 OR I.is_unique_constraint = 1 
					THEN 

						char(10)+char(10)+'ALTER TABLE ' +
						Schema_name(T.Schema_id)+'.'+T.name  + 
							+' ADD CONSTRAINT ' + I.name +
						CASE 
							WHEN I.is_primary_key=1 THEN  ' PRIMARY KEY '  
							WHEN I.is_unique_constraint=1 THEN  ' UNIQUE '
							ELSE ''
						END + 
						I.type_desc COLLATE DATABASE_DEFAULT + ' ( ' +char(10)+char(9)+
						KeyColumns + ' )  '
				ELSE

						char(10)+char(10)+'CREATE ' +
						CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END  + 
						I.type_desc COLLATE DATABASE_DEFAULT +' INDEX ' +  
						I.name  + ' ON '  + 
						Schema_name(T.Schema_id)+'.'+T.name + ' ( ' +char(10)+char(9)+
						KeyColumns + ' )  '
				END +
	
				ISNULL(char(10)+' INCLUDE ('+char(10)+char(9)+IncludedColumns+' ) ','') +
				ISNULL(' WHERE  '+I.Filter_definition,'') + char(10)+'WITH ( ' +
				CASE WHEN I.is_padded = 1 THEN 'PAD_INDEX = ON' ELSE 'PAD_INDEX = OFF' END + ', '  +
				CASE WHEN I.Fill_factor = 0 THEN '' ELSE 'FILLFACTOR = '+CONVERT(CHAR(5), I.Fill_factor) + ', '  END +
				-- default value
				'SORT_IN_TEMPDB = OFF'  + ', '  +
				CASE WHEN I.ignore_dup_key = 1 THEN 'IGNORE_DUP_KEY = ON' ELSE 'IGNORE_DUP_KEY = OFF' END + ', '  +
				CASE WHEN ST.no_recompute = 0 THEN 'STATISTICS_NORECOMPUTE = OFF' ELSE 'STATISTICS_NORECOMPUTE = ON' END + ', '  +
				-- default value 
				CASE WHEN NOT (I.is_primary_key=0 AND I.is_unique_constraint = 0) THEN 'DROP_EXISTING = OFF, '  ELSE '' END  +
				-- default value 
				'ONLINE = OFF'  + ', '  +
				CASE WHEN I.allow_row_locks = 1 THEN 'ALLOW_ROW_LOCKS = ON' ELSE 'ALLOW_ROW_LOCKS = OFF' END + ', '  +
				CASE WHEN I.allow_page_locks = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END  + ' ) ON [' +
				DS.name + ']' + char(10) +  char(10) + 'GO' + char(10)  
					AS index_code
			FROM 
				sys.indexes I  
				JOIN sys.tables T ON T.Object_id = I.Object_id   
				JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid  
				JOIN 
				(
					SELECT 
						IC2.object_id , 
						IC2.index_id , 
						STUFF(
							(
								SELECT ', ' + C.name + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END+char(10)+char(9)
								FROM 
									sys.index_columns IC1 
									JOIN Sys.columns C  
										ON C.object_id = IC1.object_id  
										AND C.column_id = IC1.column_id  
										AND IC1.is_included_column = 0 
								WHERE 
									IC1.object_id = IC2.object_id  
									AND IC1.index_id = IC2.index_id  
								GROUP BY 
									IC1.object_id,C.name,index_id 
								ORDER BY 
									MAX(IC1.key_ordinal) 
									FOR XML PATH('')
							), 1, 2, ''
						) as KeyColumns  
					FROM 
						sys.index_columns IC2  
					GROUP BY 
						IC2.object_id,
						IC2.index_id
				)tmp4  
					ON I.object_id = tmp4.object_id 
					AND I.Index_id = tmp4.index_id 
				JOIN sys.stats ST 
					ON ST.object_id = I.object_id AND ST.stats_id = I.index_id  
				JOIN sys.data_spaces DS 
					ON I.data_space_id=DS.data_space_id  
				JOIN sys.filegroups FG 
					ON I.data_space_id=FG.data_space_id  
				LEFT JOIN 
				(
						SELECT 
							IC2.object_id, 
							IC2.index_id,  
							STUFF(
								(
									SELECT 
										', ' + C.name +char(10)+char(9)
									FROM 
										sys.index_columns IC1  
										JOIN Sys.columns C   
											ON  C.object_id = IC1.object_id   
											AND C.column_id = IC1.column_id   
											AND IC1.is_included_column = 1  
									WHERE 
										IC1.object_id = IC2.object_id   
										AND IC1.index_id = IC2.index_id   
									GROUP BY 
										IC1.object_id,C.name,index_id  
									FOR XML PATH('')
								), 1, 2, '') IncludedColumns   
						FROM 
							sys.index_columns IC2   
						GROUP BY 
						IC2.object_id,
						IC2.index_id
				) tmp2   
					ON 
						tmp2.object_id = I.object_id 
						AND tmp2.index_id = I.index_id 
						and tmp2.IncludedColumns is not null
			WHERE
				isnull(I.is_primary_key,0) <> 1
				and isnull(I.is_unique_constraint,0) <> 1
		) idx

Open in new window

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:bibi92
Comment Utility
Thanks a lot but the goal is to generate script from specifical table :
$set = new-object system.data.dataset
(new-object System.Data.SqlClient.SqlDataAdapter (@"
    truncate table [ADM].[dbo].TESTFR_ddl_configuration_wk
    insert into [ADM].[dbo].TESTFR_ddl_configuration_wk
    SELECT distinct schema_name=schema_name(o.schema_id),  ddl.[object_name], ddl.new_fg_name 
	FROM [ADM].[dbo].[TESTFR_ddl_configuration] ddl
	INNER JOIN sys.all_objects o
	on ddl.[object_name]= o.[name]
	INNER JOIN sys.all_columns c
	ON c.object_id = o.object_id
	INNER JOIN  sys.types t
	on c.system_type_id = t.system_type_id 

    WHERE ddl.new_fg_name <> 'NULL' and  ddl.new_fg_name <> ''
    and  c.system_type_id IN (35,34,241,99)
	AND o.[name] NOT LIKE 'sys%'
	AND o.[name] <> 'dtproperties'
	AND o.[type] = 'U'
	AND (o.schema_id = schema_id('$Schema') or '$Schema' = '') 
	select * from [ADM].[dbo].TESTFR_ddl_configuration_wk
"@, "server=$SQLSERVER; Database=$Database; Integrated Security=sspi")).Fill($set) | out-null

Open in new window

0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
Comment Utility
0
 

Author Closing Comment

by:bibi92
Comment Utility
thanks
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article shows how a content item can be identified directly or through translation of a navigation type. It then shows how this information can be used to create a menu for further navigation.
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now