August 20, 2008 at 2:57 pm
Hi,
I have existing tables. I add two new columns to some of the tables.
After I've added the columns I can't access their names through the following statement:
SELECT COL_NAME(OBJECT_ID('table_name'), 50) AS 'Column Name'
It return NULL.
I've read some on MSDN and they say you have to either GRANT VIEW DEFINITION or use the same user that created the tables.
How do you GRANT VIEW DEFINITION? Anyone has an example?
I find it very strange since I am logged in as the same user that created the tables in the first place.
August 20, 2008 at 3:24 pm
I noticed in your COL_NAME function call that you specified 50. does the table actually have 50 columns in it?
Try this:
[font="Courier New"]SELECT *
FROM sys.columns
WHERE object_id = OBJECT_ID('schemaname.tablename')[/font]
or this:
[font="Courier New"]SELECT * FROM information_schema.columns
WHERE table_schema = 'schemaname'
AND table_name = 'tablename'[/font]
August 20, 2008 at 5:08 pm
Well, one of table has around that many columns. I have not designed the database.
I will give it a try when I get back to work tomorrow.
Thanks.
August 21, 2008 at 6:51 am
ok. That works, but I want to get a specific column name, not all the columns....
August 21, 2008 at 7:09 am
I solved it. I used your code with a cursor. That way I was able to loop through the column names.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply