Retieving default values from a table

Question

Post reply

Retieving default values from a table

ganeshmuthuvelu

SSCrazy

Points: 2177
More actions
April 16, 2003 at 3:52 pm

#80067

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

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

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 2

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 3

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

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 4

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 5

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

cneuhold Hall of Fame Points: 3375 More actions · Answer 6

That will do the trick (2nd part of the script):

http://www.sqlservercentral.com/scripts/contributions/246.asp

best regards,

chris.