1

I have 1000+ tables, SPs, and Views in my database.

How do I get a list of all "Column Names" across whole database that are being referenced in one particular table only; and not in any other tables or SPs or Views in my database?

i.e. I have Database A. which has Tables: B, C, D; Views: E, F, G; and SPs: H, I, J. Table B has 500 columns; and for example 250 columns are being referenced in other Views or SPs but 250 are not being used anywhere else. How do I get the list of Columns Names that are being used in Database B only and not anywhere else?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Vishal D Makwana
  • 13
  • 3
  • What do you mean "referenced"? – Gordon Linoff Jan 26 '20 at 23:08
  • i.e. I have Database A. which has Tables: B, C, D ; Views: E, F, G; and SPs: H, I, J. Table B has 500 columns; and for example 250 columns are being referenced in other Views or SPs but 250 are not being used anywhere else. How do I get the list of Columns Names that are being used in Database B only and not anywhere else? – Vishal D Makwana Jan 27 '20 at 00:21
  • Use SSMS and expand the Database down to the Keys. You will see the PK/FK stuff there, If you are familiar with Python it's relatively easy to create code off a schema with SQLACodeGen that can be used in SQLAlchemy. I found that to be one of the easiest ways. Plus if you use lots of database vendors, DBeaver with Eclipse ERDs are good too. – JGFMK Jan 27 '20 at 01:00
  • https://stackoverflow.com/questions/5568497/sqlserver-how-to-get-meta-data-about-tables-and-their-relationships – JGFMK Jan 27 '20 at 01:03

1 Answers1

0

This is an interesting question. This will show you all fields in all tables.

 SELECT T.name AS Table_Name ,
       C.name AS Column_Name ,
       P.name AS Data_Type ,
       P.max_length AS Size ,
       CAST(P.precision AS VARCHAR) + '/' + CAST(P.scale AS VARCHAR) AS Precision_Scale
FROM   sys.objects AS T
       JOIN sys.columns AS C ON T.object_id = C.object_id
       JOIN sys.types AS P ON C.system_type_id = P.system_type_id
WHERE  T.type_desc = 'USER_TABLE';

This will show you all field names in a specific view.

SELECT *
FROM sys.views

SELECT * 
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.View_1')

Can you start with that? Post back if you have additional questions.

ASH
  • 20,759
  • 19
  • 87
  • 200