Get all Tables, Primary Keys & Column(s)
Declare @SchemaName varchar(10) = 'dbo'
Select @@servername as [Server_Name],
db_name() as [Database_Name],
schema_name(tab.schema_id) as [Schema_Name],
tab.[name] as Table_Name,
pk.[name] as PK_Name,
substring(column_names, 1, len(column_names) - 1) as [Columns],
getdate() as [CollectionDate]
from sys.tables tab
left outer join sys.indexes pk on tab.object_id = pk.object_id and pk.is_primary_key = 1
cross apply
(
select col.[name] + ', '
from
sys.index_columns ic
inner join sys.columns col on ic.object_id = col.object_id and ic.column_id = col.column_id
where
ic.object_id = tab.object_id
and
ic.index_id = pk.index_id
order by
col.column_id
for xml path('')
) D(column_names)
where
schema_name(tab.schema_id) = @SchemaName
order by
schema_name(tab.schema_id), tab.[name]
|
|
|
|
|
|