CODEWITHSUNDEEP
sql-serverssmsdatasource
Frosty_Fraz
Frosty_Fraz

Reputation: 67

Find Data Source of a Table in SQL Server

Is there a way to find where the data in a table came from in SSMS?

For example, being able to look into a job history or the properties of a Sales table to see that the data is being populated from a website, application, ERP system, another data warehouse or database (including the name of the table or view in that DB or DW).

The information would include things like the URL of the site, the server it is hosted on, the name of the table, etc.

I have tried querying some system objects in SQL Server. I will post them below. Hopefully, this give a better idea of what I am trying to find.

select top 10 * from INFORMATION_SCHEMA.Tables

select top 10 * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'sys'

SELECT * FROM sysobjects WHERE xtype = 'U'

SELECT * FROM [msdb].[dbo].[sysjobs]

select * from sys.databases

select * from msdb.dbo.sysjobhistory

I have searched for solutions online, but to no avail. I have checked the Properties of the table as well as Dependencies, but no luck there either.

It could very well be that I have stumbled across it, but it is in a format or presented in a way in which I am not familiar. For example, a system generated ID that actually has specific meaning like redirect codes on websites (e.g. 404).

Also, this may not even be possible which is alright as well. I just would like to know whether it is possible or not.

Upvotes: 1

Views: 12218

Answers (1)

Frosty_Fraz
Frosty_Fraz

Reputation: 67

The DB server knows nothing about the data it handles, only its format. Very few details of the clients are actually known to the server. Probably you'll need to add some audit tables and record the metadata you need.

Credit to @Alejandro. I have not heard back from him so I posted his answer as an answer on his behalf.

Upvotes: 1

Related Questions