• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Retrieving defaults with their values

Hi,

I need to query DB installation on SQL Server 2005  to return all the defaults with their values. I can retrieve the names of all the defaults from following query:

SELECT name FROM sys.objects WHERE OBJECTPROPERTY(object_id, N'IsDefault') = 1

However, I will like to get the values of the defaults as well. I have searched the books online but couldnt find any suitable example.

Can you please point me in the right direction?

Thanks,

Hemal Modi
0
LawCentral
Asked:
LawCentral
  • 3
  • 3
  • 3
2 Solutions
 
YveauCommented:
Try this:

SELECT so.object_id
,      so.name
,      sc.text
FROM   sys.objects so
inner  join dbo.syscomments sc
on     so.object_id = sc.id
WHERE  OBJECTPROPERTY(object_id, N'IsDefault') = 1

Hope this helps ...
0
 
jogosCommented:
select *
from Information_schema.Columns

you will find the Column_default there
0
 
LawCentralAuthor Commented:
Thanks Yveau,

This is not I am after. For E.g. in my database the above query is returning "create default [StateWA] as 'Western Australia'" whereas I am after 'Western Australia' only. There has to be some place where the value of 'Western Australia' is stored for the Default 'StateWA'.

I know I could parse the result to retreive the value I am after but I was after a simpler solution.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
YveauCommented:
Tried this:
create table tblYveau (col1 int default 1, col2 int)
sp_bindefault 'Yveau', 'tblYveau.Col2'

Then this:
select table_name, column_default
from Information_schema.Columns
where table_name = 'tblyveau'

returned this:
table_name     column_default
-------------- --------------------------------
tblYveau       ((1))
tblYveau       create default Yveau as 'Yveau'

... so it turns out that when you do a quick-and-dirty default definition in the table def, you can get what you want ...

Hope this helps ...
0
 
LawCentralAuthor Commented:
I know what you mean. Does this imply that SQL server does not store the value for declared defaults anywhere and always executes create default statement, when a previously declared default is assigned to a column?
0
 
jogosCommented:
The ((1)) in the column_default of Information_schema.Columns IS the value.
0
 
LawCentralAuthor Commented:
Jogos,

This means you need to know which column a default is already bound to. I need to cater for all defaults irrespective of whether they are already binded or not binded to a column.

Thanks...
0
 
jogosCommented:
You can find the unbound defaults
select * from sys.objects where parent_object_id = 0 and type = 'D'

Their values I don't know,  but who creates defaults without using them? And microsoft thinks like me I found 'CREATE DEFAULT will be removed in a future version of Microsoft SQL Server. ' on books online and when you create a default by create or alter-table statement it's always bound.




0
 
YveauCommented:
default are (were) great for re-usability ... but it's correct that they are depreciated and will be removed in a future release.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now