ict.ken.be

Delivering solid user friendly software solutions since the dawn of time.

Tables by column name

Categories: SQL Server

select o.name, *
from sys.columns c
inner join sys.objects o on o.object_id = c.object_id
where c.Name = N'COLUMN_NAME_TO_FIND'

 

--get all with table row count

CREATE TABLE #foo(name varchar(128),rows int)

DECLARE @objectName nvarchar(128)

DECLARE cursorTables CURSOR FOR

select o.name 

from sys.columns c

inner join sys.objects o on o.object_id = c.object_id

where c.Name = N'u_username'

OPEN cursorTables

FETCH NEXT FROM cursorTables INTO @objectName

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO #foo

EXEC ('SELECT ''' + @objectName + ''' as NAME,COUNT(*) FROM ' + @objectName)

FETCH NEXT FROM cursorTables INTO @objectName

END 

CLOSE cursorTables

DEALLOCATE cursorTables

SELECT * FROM #foo ORDER BY rows DESC

DROP TABLE #foo