CODEWITHSUNDEEP
sqlsql-serverssis
J.Contigo
J.Contigo

Reputation: 23

Find all unique values of a column name in a SQL database

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.

Upvotes: 0

Views: 565

Answers (1)

jim
jim

Reputation: 456

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

Upvotes: 2

Related Questions