SQL Server Defaults - Xtreme Visual Basic Talk

03-23-2004, 08:03 AM

SBHang

Newcomer

Join Date: Mar 2004

Posts: 4

SQL Server Defaults

Hello,

do anyone know how I can figure out the Defaults on a SQL Server 2000 Database with the current Value of the Default and the Columns that are bounded?

I use VB 6.

Thanks :-)
SBHang

03-23-2004, 08:48 AM

Andyh

Junior Contributor

Join Date: Feb 2004

Location: Southampton, UK

Posts: 335

You haven't supplied enough info for a concise answer.
"current value" of what default?
which columns are bounded by what?
etc...

03-23-2004, 08:50 AM

MKoslof

Cum Grano Salis

Retired Moderator
* Guru *

Join Date: Jul 2002

Location: Baltimore, Maryland

Posts: 14,636

Do you want to create a table and assign new default values to specific fields, or return any current default values that are already set within your existing fields?

__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown

03-23-2004, 09:47 AM

SBHang

Newcomer

Join Date: Mar 2004

Posts: 4

Quote:

Originally Posted by MKoslof

Do you want to create a table and assign new default values to specific fields, or return any current default values that are already set within your existing fields?

Ok, sorry.

The Database, the tables and the Defaults already exits, I only want to return the Values and the binded Columns from the Defaults in the SQL Server 2000.

Default -> I know
Values -> I want to know
Binded Columns -> I also want to know

On left side in the Enterprise Manager you see under the Database Diagrams, tables, views, sp's, ... and defaults. When you click on the Defaults tab you see on the right side the current defaults. And I want to return now the Values and the bindend columns.

I hope that explanation will help you, to help me :-)

Thanks :-)
SBHang

03-23-2004, 09:55 AM

SBHang

Newcomer

Join Date: Mar 2004

Posts: 4

My Code:

Code:

Friend Property Get Defaults() As Scripting.Dictionary
   'Load all Defaults from the syscomments table, only SQL Server!!!
   Dim tmpDic As New Scripting.Dictionary
   Dim DBRecDefaults As ADODB.Recordset
   Set DBRecDefaults = New ADODB.Recordset

   Select Case DatabaseInformation.Item("DBMS Name")
      Case "Microsoft SQL Server"
         DBRecDefaults.Open "SELECT text From syscomments Where (Number = 0) And (colid = 1) And (Status = 2)", DBCon, adOpenStatic, adLockReadOnly
         Set Defaults = LoadDefaults(DBRecDefaults)
      Case "ACCESS"
         Set Defaults = tmpDic
   End Select
   
   Set tmpDic = Nothing
   Set DBRecDefaults = Nothing
End Property

Code:

Private Function LoadDefaults(DBRecDefaults As ADODB.Recordset) As Scripting.Dictionary
   'Edit the Default Data from the syscomments table
   Dim tmpDic As New Scripting.Dictionary
   Dim DataRecord As String
   Dim CutStart As Integer
   Dim CutEnd As Integer
   Dim Item As String
   Dim Key As String
   Dim tmpKey As String
   
   Do Until DBRecDefaults.EOF
      DataRecord = UCase(DBRecDefaults.Fields(0).Value)
      Key = DataRecord
      
      If IsDefault(DataRecord) Then
         CutStart = InStr(DataRecord, "CREATE DEFAULT ") + Len("CREATE DEFAULT ")
         CutEnd = InStr(CutStart, DataRecord, " ")
         If CutEnd < InStr(CutStart, DataRecord, "]") + 1 Then CutEnd = InStr(CutStart, DataRecord, "]") + 1
         
         Item = Mid(DataRecord, CutStart, CutEnd - CutStart)
        
         tmpKey = Item
         Item = Key
         Key = tmpKey
         
         Dim KeyCount As Integer
         Do Until tmpDic.Exists(Key) = False
            KeyCount = KeyCount + 1
            Key = Key + CStr(KeyCount)
         Loop

         tmpDic.Add Key, Item
      End If

      DBRecDefaults.MoveNext
   Loop
   
   
   'Test!!!
   'Set oDefault = oDatabase.Defaults("UnitPrice", "stevenb")
   
   
   
   Set LoadDefaults = tmpDic
   Set tmpDic = Nothing
End Function

With this code I get the names from all Defaults in the current Database on my SQL Server

03-23-2004, 10:25 AM

Andyh

Junior Contributor

Join Date: Feb 2004

Location: Southampton, UK

Posts: 335

You may need to trawl through the sys... tables in sql server: for example

Code:

 select o.*
from sysobjects o 
where o.name = "defaultname"

gives you the details about one particular default, the following gives you a list of defaults and their attributes:

Code:

 
select o.*
from sysobjects o 
where OBJECTPROPERTY(o.id, N'IsDefault') = 1 
and o.name not like N'#%%' 
and category & 0x0800 = 0  
order by o.name

and the following will give you some attributes of the bindings (if you know the table name:

Code:

 exec sp_MShelpcolumns N'[dbo].[tablename]'

I'll leave the rest for you to find out :-)

03-24-2004, 02:07 AM

SBHang

Newcomer

Join Date: Mar 2004

Posts: 4

Quote:

Originally Posted by Andyh

You may need to trawl through the sys... tables in sql server: for example

Code:

 select o.*
from sysobjects o 
where o.name = "defaultname"

gives you the details about one particular default, the following gives you a list of defaults and their attributes:

Code:

 
select o.*
from sysobjects o 
where OBJECTPROPERTY(o.id, N'IsDefault') = 1 
and o.name not like N'#%%' 
and category & 0x0800 = 0  
order by o.name

and the following will give you some attributes of the bindings (if you know the table name:

Code:

 exec sp_MShelpcolumns N'[dbo].[tablename]'

I'll leave the rest for you to find out :-)

@All
Thanks

Now I Know, I'm stupid.
I also exported the Value of the Default Key, but I choped it off

Now it works, thanks to all