November 6, 2017 at 11:08 am
I have more than 200 tables in one of our database and those tables contain a column name 'FirstName' and I am trying to find a person whose name is 'Desmond'. I don't want to run 'Select FirstName from table' because it is time consuming so I ended up building a query by using this tsql code SELECT 'SELECT '+c.name+' FROM '+s.name+'.'+t.name+'
'
FROM sys.columns c
INNER JOIN sys.tables t on c.object_id=t.object_id
INNER JOIN sys.schemas s on t.schema_id=s.schema_id
where c.name = 'FirstName'
But I am trying to add a where clause as well where FirstName = 'Desmond' which I am unable to.
Shortcut would be take the result, paste it in Excel and build a query from there but I would like to know what do I need to do to build a query I want?
November 6, 2017 at 11:17 am
Just?
SELECT 'SELECT '+c.name+' FROM '+s.name+'.'+t.name+' WHERE FirstName = ''Desmond'''
FROM sys.columns c
INNER JOIN sys.tables t on c.object_id=t.object_id
INNER JOIN sys.schemas s on t.schema_id=s.schema_id
where c.name = 'FirstName'
November 6, 2017 at 11:21 am
ZZartin - Monday, November 6, 2017 11:17 AMJust?
SELECT 'SELECT '+c.name+' FROM '+s.name+'.'+t.name+' WHERE FirstName = ''Desmond'''
FROM sys.columns c
INNER JOIN sys.tables t on c.object_id=t.object_id
INNER JOIN sys.schemas s on t.schema_id=s.schema_id
where c.name = 'FirstName'
Removed "Just". Now can you help?
November 6, 2017 at 11:28 am
Nevermind. Used Excel to build the query.
November 6, 2017 at 11:30 am
Syed Razi - Monday, November 6, 2017 11:08 AMI have more than 200 tables in one of our database and those tables contain a column name 'FirstName' and I am trying to find a person whose name is 'Desmond'. I don't want to run 'Select FirstName from table' because it is time consuming so I ended up building a query by using this tsql codeSELECT 'SELECT '+c.name+' FROM '+s.name+'.'+t.name+'
'
FROM sys.columns c
INNER JOIN sys.tables t on c.object_id=t.object_id
INNER JOIN sys.schemas s on t.schema_id=s.schema_id
where c.name = 'FirstName'But I am trying to add a where clause as well where FirstName = 'Desmond' which I am unable to.
Shortcut would be take the result, paste it in Excel and build a query from there but I would like to know what do I need to do to build a query I want?
How about this?SELECT sql = CONCAT('SELECT ', c.name, ' FROM ', s.name, '.', t.name, ' where Firstname = ''Desmond''')
FROM
sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name = 'Firstname';
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 6, 2017 at 12:29 pm
Phil Parkin - Monday, November 6, 2017 11:30 AMSyed Razi - Monday, November 6, 2017 11:08 AMI have more than 200 tables in one of our database and those tables contain a column name 'FirstName' and I am trying to find a person whose name is 'Desmond'. I don't want to run 'Select FirstName from table' because it is time consuming so I ended up building a query by using this tsql codeSELECT 'SELECT '+c.name+' FROM '+s.name+'.'+t.name+'
'
FROM sys.columns c
INNER JOIN sys.tables t on c.object_id=t.object_id
INNER JOIN sys.schemas s on t.schema_id=s.schema_id
where c.name = 'FirstName'But I am trying to add a where clause as well where FirstName = 'Desmond' which I am unable to.
Shortcut would be take the result, paste it in Excel and build a query from there but I would like to know what do I need to do to build a query I want?How about this?
SELECT sql = CONCAT('SELECT ', c.name, ' FROM ', s.name, '.', t.name, ' where Firstname = ''Desmond''')
FROM
sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name = 'Firstname';
Perfect. Thanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply