Wednesday, April 23, 2014

Get column names of table from SQL Azure

I have multiple Role columns and i need to get all the columns name started with Role,
My database was - SQL Azure
and this is some how different from SQL Server and here i sucked.

I wrote following query:

--select Column_name
--from Information_schema.columns
--where Table_name like 'mytable'
--AND Column_name LIKE '%Role%'


but it was giving error and processor usage become 99%. Then i found "Information_schema" not supported by SQL Azure.

So the rectified query is as follows:

select name as Column_name from sys.all_columns where object_id = (
select object_id from sys.tables where name='mytable')
AND name like '%Role%'