0

We are building a large database using SQL. Every table in the database has many columns but one of the columns in the tables tells who added the row of data. That value "Name of person" is tied to a variable in SSIS. Again, the variable tells who added the row. How can I create a query to pull back all the names in that column, no matter where it is used in the database. The value of the column is different depending on the day.

J.Contigo
  • 23
  • 6
  • 1
    Does this answer your question? [Find a value anywhere in a database](https://stackoverflow.com/questions/436351/find-a-value-anywhere-in-a-database) – Charlieface Apr 01 '21 at 21:33
  • 2
    `I have a large database in SSMS`. No you don't. `SSMS` is just a client tool. Your database is in `SQL Server` – Squirrel Apr 01 '21 at 21:36
  • I'm a little unclear on what you are trying to do. Could you click the edit button and give some sample data/structure and what you are hoping to achieve as output? – billinkc Apr 01 '21 at 23:56
  • IF you have a large database and every column has the same , then you will be pulling a value from every row in every table in a very large database. – jim Apr 02 '21 at 03:21

1 Answers1

2

RE: Every table has the same <Column_Name> ... a query to pull back all the values of that column, no matter where it is used in the database.

IF you have a large database and every table has the same column <Column_Name>, then you will be pulling a value from every row in every table ... in a very large database. Not sure that is what you want to do, but it can be easily done. The following will work even if <column_name> is only in a few tables.

Grab a list of every schema.table that contains <column_name>, then loop over it to get the the value for <column_name>. The following should work.

DECLARE @colname sysname = '<Column_name>' -- just in case it is not in every table

-- capture name of every table here
CREATE TABLE #tablename ( schema_name sysname, table_name sysname, Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)
INSERT INTO #tablename (schema_name, table_name)
SELECT s.name schemaname, t.name tablename FROM sys.columns c
INNER JOIN sys.tables t ON t.object_id = c.object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE c.name = @colname

-- capture result of query here
CREATE TABLE #result ( schema_name sysname, table_name sysname, column_value VARCHAR(100) ) 


DECLARE @i INT = 1, @imax INT
SELECT @imax = MAX(Id) FROM #tablename

-- loop over tablename
DECLARE @query NVARCHAR(255)
WHILE @i <= @imax
BEGIN

SELECT @query = N'SELECT ' + schema_name + '.' + table_name + ',' + ' <Column_Name> FROM ' + schema_name + '.' + table_name
FROM #tablename WHERE Id = @i

INSERT INTO #result ( schema_name, table_name, column_value)
EXEC sp_executesql @query

SET @i += 1

END
jim
  • 401
  • 4
  • 10
  • Mentally parsing this, when the loop completes, #result will contain the schema, table name and all the values from the column - which is currently hard coded - I assume you intended to have a `REPLACE` call in there to sub out the column name with the @colname variable? I would think you'd want a DISTINCT on the column_value to have a table that contains schema/table/unique values and then the consumer would then need to pull the unique set of column_values, yeah? – billinkc Apr 02 '21 at 16:23
  • @billnkc. Good catch. I did not intend to hard code . I wrote too quickly. But since the problem was defined on 'Column_Name", it works as is. Another good question about DISTINCT values, but that was also not a requirement. – jim Apr 04 '21 at 13:43