how in powershell (smo and scripter or other object )list tables with dependencies,indexes,FK,constrains

Question

Post reply

how in powershell (smo and scripter or other object )list tables with dependencies,indexes,FK,constrains

ebooklub

Hall of Fame

Points: 3905
More actions
February 10, 2016 at 11:44 am

#315817

Hi,
we need to output list of tables with dependencies, indexes ,FK, constrains
I used TSQL and scripted info using
---run in msdb to test it
--run with output to grid to avoid warning about tables with no index
SET NOCOUNT ON
declare
@tbl varchar(100),
@schema varchar(100) ,
@schema_table varchar(200)
if OBJECT_ID('tempdb..#tmp0')is not null
drop table #tmp0
create table #tmp0(
tablename varchar(100),
constrains text,
ObjectsThatDependOnTable text,
AllIndexes text,
DefaultConstraints text,
HasReferenceToTables text,
ReferedByTables text
)
if OBJECT_ID('tempdb..#tmp4')is not null
drop table #tmp4
create table #tmp4(
index_name varchar(100),
index_description varchar(100),
index_keys varchar(100)
)
declare C1 cursor for
select table_schema, table_name from INFORMATION_SCHEMA.TABLES
open c1
fetch c1 into @schema, @tbl
while @@FETCH_STATUS = 0
begin
set @schema_table = @schema +'.'+@tbl
insert into #tmp4
exec sp_helpIndex @schema_table
insert into #tmp0 (tablename, constrains,allIndexes, DefaultConstraints, ObjectsThatDependOnTable, HasReferenceToTables, ReferedByTables)
select
@tbl,
(
select '['+ constraint_type + ' = ' + CONSTRAINT_NAME + ']' AS [text()]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = @tbl and TABLE_SCHEMA = @schema
and
constraint_type !='CHECK'
FOR XML PATH('')
),
(
select '[Name = (' + index_name + ') Type = (' + index_description + ') keys = (' + index_keys + ')]' AS [text()] from #tmp4
FOR XML PATH('')
),
(
select
'[DFC NAME = ' +c.name + ';DFC COLUMN = ' + col.name + '; DFC VALUE = '+ definition + '] ' AS [text()]
from sys.default_constraints c
inner join sys.columns col on col.default_object_id = c.object_id
inner join sys.objects o on o.object_id = c.parent_object_id
inner join sys.schemas s on s.schema_id = o.schema_id
where s.name = @schema and o.name = @tbl
FOR XML PATH('')
),
(
SELECT
'[ '+
o.type_desc + ' '+
IsNull(object_schema_name(Referencing_ID),'*No Ref. to schema*')+'.'+
object_name(Referencing_ID) +']' AS [text()]
FROM sys.sql_expression_dependencies
INNER JOIN sys.objects AS o ON referencing_id = o.object_id
WHERE referenced_id =object_id(@schema_table)
for xml path('')
)
,
(
SELECT distinct
'['+
object_name(referenced_object_id) + ']' AS [text()]
FROM sys.foreign_keys
WHERE parent_object_id = object_id(@schema_table)
for xml path('')
)
,
(
SELECT distinct
'['+
object_name(parent_object_id) + ']' AS [text()]
FROM sys.foreign_keys
WHERE referenced_object_id = object_id(@schema_table)
for xml path('')
)
truncate table #tmp4
fetch c1 into @schema, @tbl
end
close c1
deallocate c1
Question ...
SQL server management studio provide with option to view object dependencies
example SSMS->table->View dependencies
I am assuming SMO and scripter object used to generate and present those values
Please point me to direction how to get similar view of dependencies using Powershell
so far my attempts to use Database.Script Method did not provide me any results
https://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.script(v=sql.105).aspx
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

schleep SSChampion Points: 12582 More actions · Answer 1

Found this at https://technet.microsoft.com/en-us/library/ms162153%28v=sql.105%29.aspx

Scripting Out the Dependencies for a Database in PowerShell

This code example shows how to discover the dependencies and iterate through the list to display the results.

# Set the path context to the local, default instance of SQL Server.

CD \sql\localhost\default

# Create a Scripter object and set the required scripting options.

$scrp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Scripter -ArgumentList (Get-Item .)

$scrp.Options.ScriptDrops = $false

$scrp.Options.WithDependencies = $true

$scrp.Options.IncludeIfNotExists = $true

# Set the path context to the tables in AdventureWorks2008.

CD Databases\AdventureWorks2008R2\Tables

foreach ($Item in Get-ChildItem)

{

$scrp.Script($Item)

}

ebooklub Hall of Fame Points: 3905 More actions · Answer 2

$scrp.Script($Item) does what it have to do ... generate object script in text format to file or host

what I am looking for is something similar to

$scrp.option | select indexes, fk, constrains

but $scrp does not have those properties , I do not know how get correct values

schleep SSChampion Points: 12582 More actions · Answer 3

Ah, right!

See these Options in the Smo.Transfer class.

$xfr.Options.DriAll = $true

$xfr.Options.DriForeignKeys = $true

$xfr.Options.DriUniqueKeys = $true

$xfr.Options.DriChecks = $true

$xfr.Options.DriUniqueKeys = $true

$xfr.Options.ClusteredIndexes = $true

$xfr.Options.NonClusteredIndexes = $true

then, call the script method on the transfer object:

$xfr.Script()

ebooklub Hall of Fame Points: 3905 More actions · Answer 4

Thank you

xft.Script -- does not exist but $xfr.ScriptTransfer() provide readable output

I also found example providing me with result more close to my request

https://www.simple-talk.com/sql/t-sql-programming/dependencies-and-references-in-sql-server/

schleep SSChampion Points: 12582 More actions · Answer 5

Say, that's a good link. Not sure how I never came across it..

Thanks for posting.

P