I need a query to get pk, fk, indexes, references tables and attributes in sql and export to excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2560204%22%20slang%3D%22en-US%22%3EI%20need%20a%20query%20to%20get%20pk%2C%20fk%2C%20indexes%2C%20references%20tables%20and%20attributes%20in%20sql%20and%20export%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2560204%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%20%2C%3C%2FP%3E%3CP%3EI%20want%20to%20get%20with%20SQL%20all%20information%20about%20all%20the%20tables%20in%20my%20database%2C%20whit%20this%20order%3A%3CBR%20%2F%3EFirst%2C%3CSTRONG%3E%20the%20Table%20Name%2C%20Attribute%2C%20Data%20Type%2C%20Lenght%2C%20Constraint(PK%2C%20FK)%2C%20and%20ReferencesTable.%3C%2FSTRONG%3E%3CBR%20%2F%3EBecause%20I'm%20doing%20a%20dictionary%20of%20data%20in%20excel.%20So%20I%20want%20to%20export%20the%20result%20of%20that%20query%20to%20my%20excel.%3C%2FP%3E%3CP%3EFor%20example%20this%20query%20brings%20me%20the%20PK%20for%20all%20the%20tables%20that%20I%20have%20in%20my%20database%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3Eselect%20schema_name(tab.schema_id)%20as%20%5Bschema_name%5D%2C%20%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Etab.%5Bname%5D%20as%20table_name%2C%20%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Epk.%5Bname%5D%20as%20pk_name%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Esubstring(column_names%2C%201%2C%20len(column_names)-1)%20as%20%5Bcolumns%5D%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Efrom%20sys.tables%20tab%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Eleft%20outer%20join%20sys.indexes%20pk%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Eon%20tab.object_id%20%3D%20pk.object_id%20%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Eand%20pk.is_primary_key%20%3D%201%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Ecross%20apply%20(select%20col.%5Bname%5D%20%2B%20'%2C%20'%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Efrom%20sys.index_columns%20ic%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Einner%20join%20sys.columns%20col%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Eon%20ic.object_id%20%3D%20col.object_id%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Eand%20ic.column_id%20%3D%20col.column_id%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Ewhere%20ic.object_id%20%3D%20tab.object_id%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Eand%20ic.index_id%20%3D%20pk.index_id%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Eorder%20by%20col.column_id%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Efor%20xml%20path%20('')%20)%20D%20(column_names)%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Eorder%20by%20schema_name(tab.schema_id)%2C%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3Etab.%5Bname%5D%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EI%20try%20many%20queries%20but%20I%20can't%20create%20one%20that%20brings%20me%20in%20ONE%20query%20all%20that%20information%20that%20I%20want.%3C%2FP%3E%3CP%3EIt%20has%20to%20be%20IN%20ONE%20QUERY.%3C%2FP%3E%3CP%3EI%20put%20here%20an%20example%20for%20the%20columns%20that%20I%20need.%3C%2FP%3E%3CP%3EI%20hope%20you%20can%20help%20me!%20Thanks.%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Captura.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F296641i7CE00F49D33A6A1E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Captura.PNG%22%20alt%3D%22Captura.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi everyone! ,

I want to get with SQL all information about all the tables in my database, whit this order:
First, the Table Name, Attribute, Data Type, Lenght, Constraint(PK, FK), and ReferencesTable.
Because I'm doing a dictionary of data in excel. So I want to export the result of that query to my excel.

For example this query brings me the PK for all the tables that I have in my database:

select 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]
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)
order by schema_name(tab.schema_id),
tab.[name]

I try many queries but I can't create one that brings me in ONE query all that information that I want.

It has to be IN ONE QUERY.

I put here an example for the columns that I need.

I hope you can help me! Thanks.
Captura.PNG

0 Replies