bibi92
asked on
generate a script by object type TABLE, FOREIGN_KEY, TRIGGER, CHECKCONSTRAINT
Hello,
I search to modify this script to generate a script by object type TABLE, FOREIGN_KEY, TRIGGER, CHECKCONSTRAINT :
 How can I do it, please ?
Â
 Thanks
Â
 Regards
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))
}
 How can I do it, please ?
Â
 Thanks
Â
 Regards
What exactly are you trying to achieve?
ASKER
Hello,
I try to generate a script by object type :
new.Create.Tables.sql
new.Create.Triggers.sql
new.Create.ForeignKeys.sql
new.Create.CheckConstraint s.sql
Thanks
Regards
I try to generate a script by object type :
new.Create.Tables.sql
new.Create.Triggers.sql
new.Create.ForeignKeys.sql
new.Create.CheckConstraint
Thanks
Regards
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks