April 16, 2003 at 3:52 pm
Hello,
Is there a system sp or something that returns all the column names and its default values, in a table?.
I am basically looking for a stored procedure, which takes a table name as a parameter and returns the column names and the default values (if at all, defined)..
Any thoughts?
Ganesh
April 16, 2003 at 3:56 pm
Might try something like this:
insert into <your table> select column_name, column_default from information_schema.columns
where table_name = '<table with default values>'
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 17, 2003 at 8:35 am
Thank you Greg,
Can you also please tell me what is the permission level required for executing this statement?. I mean, is it default to "public" role?.
The reason, I ask this question is, I tried executing this statement, as a user who is a member of only "public" role, it did not return any rows for a particular table, but for user who is a member of sysadmin, it did return rows..
Your thoughts?
Thanks,
Ganesh
April 17, 2003 at 9:09 am
I tried it with a user that had no permissions to anything, and it still seems to return data. What version of SQL Server are you running? I ran my test on SQL 2000 SP3.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 17, 2003 at 12:30 pm
Greg,
Mine is also 2000 with SP3. Let me be more specific. If the user does *not* have select permission on a table and this query is executed by him, it basically does not return any rows.. (I rather was expecting an error message to be thrown..)
For the table, which you tested, just deny select, update and delete privileges and try running it.. you will see that it will not return any rows.. niether en exception.. Anyway, this is ok with me but still would like to know from you..
Also, Is there a way similar to this to get the column descripitons also (the ones which we normally give while designing the table) given a table name?.
Thanks for your time.
Ganesh
April 17, 2003 at 2:10 pm
You can get the description for each field in a table using the following TSQL:
select objname column_name, value Description from ::fn_listextendedproperty(NULL, N'user', N'dbo', N'table', N'<your table>', 'column',default)
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 22, 2003 at 12:41 am
That will do the trick (2nd part of the script):
http://qa.sqlservercentral.com/scripts/contributions/246.asp
best regards,
chris.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply