Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server Express - Change column default value

Posted on 2006-05-04
12
Medium Priority
?
597 Views
Last Modified: 2007-12-19
I have a table called 'Users'
In this table I have a field called 'Enabled' that is a tinyint with a length of 1 (0=disabled, 1 = enabled)

Right now, the default value for the enabled field is 0. I want to programmatically check the default value on startup, and if it is not 1, then to programmatically change the default value to 1.

I am using VS2005 pro, SQL Server express 2005, ODBC and SqlClient.SqlDataAdapter to access the database and writing everything in VB for a windows desktop application.

I have tried many things and I can't get it work.
What's the magic code to do what I would like?
0
Comment
Question by:Mystify
  • 7
  • 4
12 Comments
 
LVL 4

Expert Comment

by:Sowmya_K
ID: 16614948
Here's the magic code

UPDATE Users SET Enabled = 1 WHERE Enabled = 0
0
 
LVL 2

Author Comment

by:Mystify
ID: 16615117
That will only set the ROW data equal to the specified value, but it doesn't change the fields default value. All new rows that are added to the table that do not set the enabled field value will still have a value of 0.

I need to alter the column's default value from 0 to 1.
0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 1500 total points
ID: 16642301
First, you'd need to create a default for your value of 1.

From within your database run the following code:

CREATE DEFAULT myOneValue AS 1
GO

Then bind the default to your column with the following code:

sp_bindefault myOneValue, 'Users.Enabled'
GO

I would create a stored procedure in your database that has the sp_bindefault code and set it to be a startup procedure.  Then, regardless of how the default has been set, it will set the default to 1.

To wrap it all up, you could create a single, startup stored procedure that will do it all.  The first step would be to create a default for your '1' value, if it doesn't exist.  The second step would be to unbind the current default from the table using sp_unbindefault or ALTER TABLE.  The final step would be to bind the new default.






0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 2

Author Comment

by:Mystify
ID: 16642488
interresting.... any idea on how to create and alter stored proceedures?

Just to clarrify then...

I run a query: "CREATE DEFAULT myOneValue AS 1"
in which myOneValue  is the what? name of the stored proceedure?
and after I run that query, I run another query: "sp_bindefault myOneValue, 'Users.Enabled'"
and that's it? So I can use this same proceedure to set any default on any field by just changing the "AS" value in the first query?

What is the syntax then to ALTER that default so that I can apply a different default value to a different field?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16643293
Have a look at Books On Line for the syntax of CREATE DEFAULT.  myOneValue is just an arbitrary name I picked for the default.  

Of course, if you already have a default on Users.Enabled you can't bind another default to it, you can only have one default per column.  You have to remove the original default.  That can be done with either sp_unbindefault, or ALTER TABLE ... DROP CONSTRAINT.  Using the ALTER TABLE syntax requires that you know the name of the constraint.  You can find the name of the constraint with this query:

Select name From sysobjects Where id In (Select cdefault From syscolumns Where Name = 'Enabled')

You can't directly alter a constraint.  First, You have to remove the existing default, then apply a new one.

Creating and altering stored procedures is something entirely different than your original question.
0
 
LVL 2

Author Comment

by:Mystify
ID: 16643511
I will give these technquies a try tommorrow and let you know how it goes.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16643539
I have a working model you may want to try.  If you need it please post here.
0
 
LVL 2

Author Comment

by:Mystify
ID: 16648343
ok, cmangus

I will take you up on your offer to see what you have for source.

Now, to a minor extent I have been partially successfully getting this working. Let me explain a little more what is happening and what I need to do.


When my application starts up, it looks to see if the database exists.

strSQL = "IF EXISTS (SELECT * FROM master..sysdatabases WHERE Name = '" & DBName & "')" & vbCrLf & " SELECT '1' " & vbCrLf & " ELSE " & vbCrLf & " SELECT '0' "
conn.SelectCommand.CommandText = strSQL
conn.Fill(rstRS)

if rstrs.Rows(0).item(0)="0" then the database doesn't exist.

if it doesn't, I prompt the user for the path where the database is to be stored, then I execute the following:

      strSQL = "CREATE DATABASE " & DBName & " " & _
                    "ON (" & _
                    "  NAME = " & DBName & "_data, " & _
                    "  FILENAME = '" & Path & DBName & ".mdf '," & _
                    "  SIZE = 2MB," & _
                    "  MAXSIZE = UNLIMITED," & _
                    "  FILEGROWTH = 1MB) " & _
                    "LOG ON(" & _
                    "  NAME = " & DBName & "_log," & _
                    "  FILENAME = '" & Path & DBName & ".ldf'," & _
                    "  SIZE = 1MB," & _
                    "  MAXSIZE = UNLIMITED," & _
                    "  FILEGROWTH = 1MB) "

            conn.SelectCommand.CommandText = strSQL
            conn.SelectCommand.ExecuteNonQuery()

So now that the database has been created, I connect to it and continue with my applicaiton.


Now, I do a check to see if each table in my database actually exists or not.  I do it in a similar to the way I do the database check.

            strSQL = "IF EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id(N'[dbo].[" & Table & "]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) " & vbCrLf & "SELECT '1' " & vbCrLf & " ELSE " & vbCrLf & "SELECT '0'"
            conn.SelectCommand.CommandText = strSQL
            conn.Fill(rstRS)

if rstrs.Rows(0).item(0)="0" then the table doesn't exist so I need to create it, If it DOES exist, then I need to make sure that all the fields in the table exist and are the correct type (in case a software update has occured and the database is old)

So, the first thing I do is create my SQL query to create the table.

For example purposes, here is a (cut down) version of my users table.

strSQL = "CREATE TABLE Users (" & _
              " id INT IDENTITY PRIMARY KEY," & _
              " Name VARCHAR(255)," & _
              " Password VARCHAR(255)," & _
              " FullName VARCHAR(255)," & _
              " Enabled TINYINT Default 1," & _
              ")"

So if the table didn't exist, I run that query and it creates the table.
No problem.

Now, if it DID already exist, then I need to make sure that all 5 fields actually exist, are the correct data type, have the correct IDENTITY  and PRIMARY KEY flags, and the correct default value.

So, I parse my query and extract all the info I need in each field. I then run this query on the users table: strSQL = "sp_columns " & Table
and start to loop through each one, looking to make sure all is correct.
One of my checks (and the first problem) is:

If Not rstRS.Rows(j).Item("COLUMN_DEF").Equals(Fields(i).DefaultValue) Then blnModifyDefault = True
(Fields(i).DefaultValue in this case would be a integer value of 1)

if blnModifyDefault  is true, then that means that the default value of the field is NOT what I need it to be.
Now, the default value of a field when it is created by CREATE TABLE query is, for some reason, always  surrounded by 2 sets of brackets... so COLUMN_DEF would return "((1))"

For the sake of continuing, let's assume that the current default of the enabled field is 0, as the previous version of the software created the table with a default value of 0

HOWEVER, when I used your method of binding defaults, the COLUMN_DEF would return the actual query of the default, in my case, it returned "CREATE DEFAULT users_enabled AS 1" (I am using <table>_<field> as the default name to make sure it's unquie)


So my first problem is how do I reliably detect what the current field default value is, if there is one?




So, let's assume that I figured this out, then I need to modify the default value of the field if it is not correct.

So, to delete the default that was created by the CREATE TABLE query, I loop through all the contrainsts using the following query:

strSQL = "SELECT name FROM sysobjects WHERE xtype = 'DF' AND parent_obj = object_id('" & Table & "')"

If I find one that starts with "DF__Users__Enabled" then I delete it. This removes the default value of the field.

HOWEVER!

PROBLEM:
I am also using Microsoft SQL Server MAnagement Studio to monitor the database as I make changed and what I have noticed is that in some wierd cases, the field will have a default value, but no DF contraint. I do not know how this is, what causes it, or how to fix it... but if I since no constraint was found, the default was not removed... and when I try to set the new default, I get an error saying it already has a default.

How do I reliably delete the default value of a field?
(provided I actually need to)


So, let's assume for the moment that the table no longer has a default value applied via CREATE TABLE.

I now look to see if my database default exists already using the following query:
strSQL = "IF EXISTS (SELECT name FROM sysobjects WHERE name = '" & Table & "_" & Fields(i).Name & "' AND type = 'D') " & vbCrLf & "SELECT '1' " & vbCrLf & " ELSE " & vbCrLf & "SELECT '0'"

Table & "_" & Fields(i).Name in this case would be "Users_Enabled"

If it is found, then I need to delete it so the new one can be created.... the first thing I do is try to unbind it from the table.

PROBLEM: How do I know if it is bound to a table, and which ones?

So, once I figure out how to actually find what tables it was bound to (and techincally, it should only be ever bound to a single field) I run this:

strSQL = "sp_unbindefault " & Table & "_" & Fields(i).Name & ", '" & Table & "." & Fields(i).Name & "'"
Which would eqate to: strSQL = "sp_unbindefault Users_Enabled, 'Users.Enabled'"

THIS FAILED when I tried it for 2 reasons:
1. If it is not bound, it failed. I simply put it in an error trap to take care of it
2. It WAS bound, but failed and I get this error: The data type or table column 'Users_Enabled' does not exist or you do not have permission. Which is odd because I actually have both.

Assuming I could actually unbind it, I then run strSQL = "DROP DEFAULT " & Table & "_" & Fields(i).Name  to delete it. Which works if it is not longer bound.

At this point, I can now create and bind the new default value.

strSQL = "CREATE DEFAULT " & Table & "_" & Fields(i).Name & " AS " & Fields(i).DefaultValue
which = "CREATE DEFAULT Users_Enabled AS 1"

and then

strSQL = "sp_bindefault " & Table & "_" & Fields(i).Name & ",'" & Table & "." & Fields(i).Name & "'"
which = "sp_bindefault Users_Enabled, 'Users.Enabled'"

Both of which appear to work.



What have written is a generic routine which appears to work, but just has some basic issues.... Can you help? This seems very complicated. Is there an easier way?
Is my proceedure wrong? I thought about doing an "alter default" instead of a delete and re-create, but will that work if it is bound? and is there an "alter default"?

Thanks for any help you can provide.

0
 
LVL 2

Author Comment

by:Mystify
ID: 16648398
I found out what was wrong with my sp_unbindefault query. I just changed it to be: "sp_unbindefault  'Users.Enabled'" and that worked. I didn't have to specify the default itself.

However, I still have the problem with detecting if it is bound.
0
 
LVL 2

Author Comment

by:Mystify
ID: 16648469
oh, and how do you get the current default value of a binding default? Meaning, if Users_Enabled was set to 1, how do I read that back to find make sure it's correct?
0
 
LVL 2

Author Comment

by:Mystify
ID: 16648671
And yet, another problem.

In trying to find if there is a constraint on field, the query didn't work, so when I tried this:
strSQL = "SELECT name FROM sysobjects WHERE name LIKE '%DF%" & Table & "%" & Fields(i).Name & "%' AND parent_obj = object_id('" & Table & "')"

I found another problem.

I have a table called "AccessLevels" and in it are lots and lots of fields, several of which are called things like "OptionsCore", "OptionsBackup", etc.

in the constraints list, I have this:

DF__AccessLev__Optio__1367E606
DF__AccessLev__Optio__145C0A3F
DF__AccessLev__Optio__145C0A3F
DF__AccessLev__Optio__145C0A3F

and so on.


How do I tell which contrainst goes to which field??
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 16649873
Hi Mystify,

First, here's a working model I used.

CREATE PROCEDURE SetMyDefault
AS

Declare @mySQL nvarchar(500), @myName varchar(100)

Set NoCount On

/*
   First, check for and create the new default if it doesn't exist.  Use dynamic SQL because technically you can't
   use 'CREATE DEFAULT' syntax in a stored procedure.
*/
If Not Exists (Select * From dbo.sysobjects where id = object_id(N'[dbo].[myOneValue]') and OBJECTPROPERTY(id, N'IsDefault') = 1)
      Begin
            Set @mySQL = N'CREATE DEFAULT myOneValue As 1'
            Exec sp_executeSQL @mySQL
      End

/*
   Then, if the column has an existing default, unbind or drop it.
*/
If Exists (Select name From sysobjects Where id In (Select cdefault From syscolumns Where Name = 'Enabled'))
      Begin
            /*
               Depending on how the existing default was created, we have to drop it differently.  If it was
                   created in Table Designer it will begin with the 3 character prefix of DF_.  If it was created
                   using ALTER TABLE syntax it will have a different name.  The existing default will need to be
                   dropped in a way specifically relating to how it was created / applied / bound.
            */
            Select @myName = name From sysobjects Where id In (Select cdefault From syscolumns Where Name = 'Enabled')
            If Left(@myName, 3) = 'DF_'
                  Set @mySQL = N'ALTER TABLE Users DROP Constraint ' + @myName
                Else
                  Set @mySQL = N'Exec sp_unbindefault ''Users.Enabled'''
            Exec sp_executeSQL @mySQL
      End

/*
   Now that we know we have the new default, and the old default, if there was one, is unbound, bind the new default
   to the column if the table exists.
*/
If Exists (Select * From dbo.sysobjects Where id = object_id(N'[dbo].[Users]') And OBJECTPROPERTY(id, N'IsUserTable') = 1)
      Exec sp_bindefault myOneValue, 'Users.Enabled'

When you use this call this SP it will detect a default on Users.Enabled, no matter what that default may be, then it will remove it and set it to the new default.  In looking at it I see that it will need to take into account the fact that you may have a column called Enabled in more than 1 table.

Now I'll try to go back and answer your questions.

> So my first problem is how do I reliably detect what the current field default value is, if there is one?

Do you want to know what the current default is, or just if there is one?  It seems the only thing we really need to know is if there is one, because we have to drop it before we can bind the new one.  If there is a value in the cdefault column in syscolumns then the column has a default.

> PROBLEM:
> I am also using Microsoft SQL Server MAnagement Studio to monitor the database as I make changed and
> what I have noticed is that in some wierd cases, the field will have a default value, but no DF contraint. I do
> not know how this is, what causes it, or how to fix it... but if I since no constraint was found, the default was > not removed... and when I try to set the new default, I get an error saying it already has a default.

> How do I reliably delete the default value of a field?
> (provided I actually need to)

I'm not sure why sometimes you have a default value but can't find the DF constraint.  You may want to try also looking for an xtype of 'D' in sysobjects in addition to 'DF'.

> I thought about doing an "alter default" instead of a delete and re-create, but will that work if it is bound? and > is there an "alter default"?

I don't believe there is an ALTER DEFAULT command.

> How do I tell which contrainst goes to which field??
 
You can look at the constraints on fields by querying the INFORMATION_SCHEMA.COLUMNS view paying particular attention to the COLUMN_DEFAULT field.

I hope I've answered all your questions.


0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
What we learned in Webroot's webinar on multi-vector protection.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question