Need Help With A Query That Retrieves DEFAULT Constraints
Dec 6, 2005
Hi guys,
I am trying to write a query that seaches for all DEFAULT constraints in a database and prints the column with the default value, the value of the default, and the name of the default constraint.
So far I have the two queries that let me get all the information I need.
SELECT Name FROM sysobjects
WHERE xtype = 'D'
SELECT column_default, column_name FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_default IS NOT Null
I am unable to merge them into one query. I cannot seem to find a commun field to both of them.
We've got a product which uses merge replication with anonymous pullsubscriptions. At most custome sites it's running on SQL Server 2000, a few with SQL 2005, which is running wich replication compatibility level 80 due to . As it happens, db schema changes. So I have to drop a column with a default constraint. First the constraint, then the column. This works excellently on the publisher - but not on the subscriber
The schema script 'exec sp_repldropcolumn '[dbo].[role_modul_rmd]', 'rmd_modul_enabled', 1' could not be propagated to the subscriber. (Quelle: MSSQL_REPL, Fehlernummer: MSSQL_REPL-2147201001)Hilfe abrufen: http://help/MSSQL_REPL-2147201001The object 'DF__role_modu__rmd_m__3119DB2C' is dependent on column 'rmd_modul_enabled'. (Quelle: MSSQLServer, Fehlernummer: 5074)Hilfe abrufen: http://help/5074ALTER TABLE DROP COLUMN rmd_modul_enabled failed because one or more objects access this column. (Quelle: MSSQLServer, Fehlernummer: 4922)Hilfe abrufen: http://help/4922
Hi, I want to drop a column from a table with default constraint. It is giving me error.. ------------------ Server: Msg 5074, Level 16, State 1, Line 1 The object 'DF__ACTIVITY___ROLLU__108B795B' is dependent on column 'ROLLUP_BGT_COST_FIXED'. Server: Msg 4922, Level 16, State 1, Line 1 ALTER TABLE DROP COLUMN ROLLUP_BGT_COST_FIXED failed because one or more objects access this column. -------------------------
Here is the drop statement ----------- DROP STATISTICS ACTIVITY_BASELINE.ROLLUP_BGT_COST_FIXED ALTER TABLE ACTIVITY_BASELINE DROP COLUMN ROLLUP_BGT_COST_FIXED; ------------------------
I am doing a little research on Google about this topic and I ran intothis thread:http://groups.google.com/group/micr...dc13d4ee6758966I read SQL Server MVP Louis Davidson's post saying:"Actually they are more likely to drop the concept of bound defaults.Constraints are the standard way to do this, and really should be the wayyou create defaults anyhow."Even I read in the Microsoft SQL Server Introduction (SQL 7 bookpage 244, however we're using SQL Server 2000):"Constraints define rules regarding the values allowed in columns and arethe standard mechanism for enforcing integrity, preferred over triggers,rules, and defaults. They are also used by the query optimizer to improveperformance in selectivity estimation, cost calculations, and queryrewriting."Why constraint defaults are better? The second sentence about constraintshaving better optimization, I am guessing they don't mean this aboutDefault Constraints, rather the other type of constraints?Because I don't see how a Default Constraint have anything to do withperformance? Isn't default only to do with new records being created?At work we are setting all tables' columns to have constraint defaultsof 0 or ' ' (space character) in order not to have any column with theNULL value. Therefore we have dozens of files containing statements like:alter table TABLE1 add constraint TABLE1_ID_DFDEFAULT(' ') FOR IDgoalter table TABLE1 add constraint TABLE1_QUANTITY_DFDEFAULT(0) FOR QUANTITYgoFirst I was thinking to create 3 SQL Defaults called:DefaultZeroDefaultSpaceDefaultDateand then bind these defaults to all the columns of all tables excludingprimary keys. After creating the tables I would enumerate throughall the columns and bind one of these three Defaults based on theirdatatype:number = DefaultZerotext type = DefaultSpacedate type = DefaultDateAnd then unbind the ones that we specifically need to specify otherdefault values.So my question is should I do this by using sp_binddefault or stickwith using Default Constraints inside a table/columns loop code?Thank you
Does anyone know how to tell sp_rename to look in a schema other than the default. The code below reproduces the problem.
-- WORKS IN default schema -- create table dbo.TestDF1( dfField intconstraint DF1 default 0 ) go
sp_rename 'DF1', 'DF2', 'OBJECT' go
Select name From sys.default_constraints where object_name(parent_object_id) = 'TestDF1' go
drop table dbo.TestDF1 go
-- DOESN'T WORK IN added schema -- create schema TestSchema go
create table TestSchema.TestDF2( dfField intconstraint DF3 default 0 ) go
sp_rename 'DF3', 'DF4', 'OBJECT' go
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 315 Either the parameter @objname is ambiguous or the claimed @objtype (OBJECT) is wrong. --------------------------- drop table TestSchema.TestDF2 go
I am using the "Transfer SQL Server Objects Task" to copy some tables from database A to database B including data.
The tables, primary key constraints, Foreign key, data and all transfers nicely except for "DEFAULT CONSTRAINTS" on the tables.
I have failed to find any option in the "Transfer SQL Server Objects Task" task to explicitly say "copy default constraints". So I guess logically it should happen automatically but it doesn't. I hope it is not a bug :-)
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this? Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Hi, I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error. e.g MOB 401.908.804 - Fails 0401.907.324 - okay 8239 9082 (pager) - fails Anyone got an idea on how to fix this???? Regards.. Peter.
I have 595 default constraints in my database. I can return a list of them using the following:
select * from sys.default_constraints
Is there a way I can return a list of just the ones where NULL is still allowed? I want to update all of the columns with a default value to not allow NULLs.
I am wondering if there is any statement which I can write at the start of my script that causes some referencial integrity problem so that foriegn keys dont regulate my script and foriegn keys allow it to run.
Hi, I have two tables (Accounts and Contacts) that have a many-to-many relationship, maintained by the AccountContactLinks table. I would like to populate a Contacts DropDownList with all of the Contacts associated with the Account Selected in Accounts DropDownList. Here is the (SP) SQL Query I'm trying to make work: SELECT Contact.ContactID, Contact.ContactLastName, Contact.ContactFirstName, Contact.ContactLastName + ', ' + Contact.ContactFirstName AS ContactName FROM Contact INNER JOIN AccountContactLinks ON Contact.ContactID = AccountContactLinks.ContactID WHERE (AccountContactLinks.AccountID = @AccountID) ORDER BY Contact.ContactLastNameEND I keep getting the following error: Failed to enable constraints. One or more rows contains values violating non-null, unique or foreign key constraints. I haven't implemented any non-null, unique or foreign key constraints between any of these tables, so suspect that I've got the SQL Query wrong. Thanks very much. Regards Gary
I want to retrieve the datas from the table with condition if DetailID is not null and DetailID is not guid.empty then it return only the DetailID = @DetailID
I have come across this problem with SQL server both on 2000 and 2005. I am stating an example here.
I have two partitioned tables and a view on top of both tables as below: create table [dbo].[Table_1] ( [TableID] INTEGER PRIMARY KEY NONCLUSTERED CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1 AND 999), [AnyOtherColumn] int NOT NULL , ) ON [Primary] GO
create table [dbo].[Table_2] ( [TableID] INTEGER PRIMARY KEY NONCLUSTERED CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1000 AND 1999), [AnyOtherColumn] int NOT NULL , ) ON [Primary] GO create view TableView as select * from Table_1 union all select * from Table_2 GO
Note the NOT FOR REPLICATION clause on the check constraint on the TableID column.
I then ran the query execution plan for the following query on both SQL server 2000 and 2005. select * from TableView where TableID = 10
On both the versions the execution plan shows and Index seek on both the tables in the view. This means that my partitioning is not working. If I remove the primary key constraint from the TableID column, the same query on the view shows a table scan on all the underlying tables. This is even worse.
Next, create the same tables and views again, now without the NOT FOR REPLICATION clause on the check constraint as show below: create table [dbo].[Table_1] ( [TableID] INTEGER PRIMARY KEY NONCLUSTERED CHECK ([TableID] BETWEEN 1 AND 999), [AnyOtherColumn] int NOT NULL , ) ON [Primary] GO
create table [dbo].[Table_2] ( [TableID] INTEGER PRIMARY KEY NONCLUSTERED CHECK ([TableID] BETWEEN 1000 AND 1999), [AnyOtherColumn] int NOT NULL , ) ON [Primary] GO
create view TableView as select * from Table_1 union all select * from Table_2 GO
Now run the query execution plan for the same query again.
select * from TableView where TableID = 10
This time you would see that it does an index scan only on the first parititon table. This time it proves that the partitioning works.
I would like to know why does the NOT FOR REPLICATION clause in the check constraint make such a huge difference?
I am trying to query my records using a text field. However, I want my results to be all when there the text field is blank. Currently I have 257134 records but when I use my code, it is only showing 237423 records. Here is my code:
SELECT COUNT(ID) AS Count FROM ViewAllEmployees WHERE (JobDescription1 LIKE '%')
Any ideas as to what wildcard to use to show NULL and Not NULL records?
How can I give default value to a field in a table which is already created, i.e. there is a table test and it have field test1 which is int(4). Now, I want to give a default value 0 to this field. As I am not able to access Enterprise Manager, I want to do it using Query Analyzer. How can I do this using Query Analyzer?
i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?
this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in
Hi, I have report parameter and its values are static lik (ABC, DEF, GHI ) etc. I want to select all as a default for this parameter. How I can do this?
I have several default constraints defined on a table. When I use theObject Browser and expand the constraints for this table andright-click and then select "Script Object to New Window As Create", acreate constraint statement for a different default constraint isdisplayed than the one I just right-clicked on. For example, I clickon constraint "DF_C" and it shows me "DF_B".The last time I encountered this, the solution was to dump contents ofthe table into another, drop, recreate it, and restore the contents.That's not a good option this time.Is there another way to fised this or at least navigate the catalog tofind out what is "off" about this?Thanks
My Integrity job started failing recently with the following error. Msg 701, Level 17, State 123, Line 1 There is insufficient system memory in resource pool 'default' to run this query. Process Exit Code 1. The step failed.
select @@ version Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) Jul 9 2014 16:04:25 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) System is having 4GB RAM and SQL is using most of it. It has 2 processors.
is there a way to change SSMS 2012 default query save location?
Scenario:
1. Open SSMS 2. Create new query 3. Click Save
I see "DocumentsSQL Server Management Studio" folder, but I want to change it to be "d:". How do I do this? I tried:
1. [URL]- in folder "DocumentsSQL Server Management StudioSettingsSQL Server Management Studio" there is a file NewSettings.vssettings, setting it to "d:" or "d:" didn't work.
2. Changing HKEY_CURRENT_USERSoftwareMicrosoftMicrosoft SQL Server100ToolsShell VisualStudioProjectsLocation didn't work too. There is no "Shell" under "110Tools"
Did I do something wrong, or is there another way?
Hi, Does anyone have a workaround or know of a fix to this problem: Default value set to 'date pick' from date currently within field by setting value equal to that field . ie if date is 01/01/2010 date picker opens in Jan 2010 - works ok. However, once published to Sharepoint and run through browser the Date Picker ignores the default value and the date picker opens for today. ie April 2008.
I'm using MS SQL 2000 with enterprise manager I have a customers table called high_customer, it has a primary key called IDI have an invoices table called high_invoices, it has no primary keys but it does have a column for customer ID.I want to set it so if a customer is deleted from the customers table, any invoices with that customerID are deleted from the invoices table.Do I need to specify a constraint? If so how do I do it in enterprise manager.Thanks
Hi, I have the following problem: I have a table called Jobs with the fields: JobNumber, Name, Customer, ... And a table called Customers with the fields: ID, Name, Address, ... Obviously Jobs is linked to Customers with the Customer<->ID fields. I want to set it up so that if a Customer is deleted, then any Jobs that had that customer listed now have the Customer field set to NULL. Can I do this with a constraint, or will I need to use a trigger? Cheers,Little'un
Hello, I am not a DBA and I need to do the following. I have 2 tables A and C and both have a common fileds say emp.Emp is pkey in C . There can be a record in C but not in A.If a record is inserted in A then it checks for the same employee info in C .If not found it do not allow to insert a record in A. How to go for it? Thanks!
Hiya peops im creating a constraint using enterprise manager for 1 of my tables n was wondering how u constructively create a constraint expression, there is a specific style,right??
i have added my tables and defined foreig keys and primary keys for my table, but when i try to put the data it gives me some error reagards to parent key..and it wont let me drop the table... can anybody tell me how to drop the foregin key..i have tried (
ALTER TABLE module DROP CONSTRAINT ATTENDANCEID_FK;) this formula and it wont work,
heres my tables and relationships:
CREATE TABLE ATTENDANCE (ATTENDANCEID char(4) NOT NULL, StudID char(4) NOT NULL, ModuleID char(4) NOT NULL, Week1 VARCHAR(10), Week2 VARCHAR(10), Week3 VARCHAR(10), Week4 VARCHAR(10), Week5 VARCHAR(10), Week6 VARCHAR(10), PRIMARY KEY (ATTENDANCEID));
ALTER TABLE ATTENDANCE ADD FOREIGN KEY (studID) REFERENCES student(studID); ALTER TABLE ATTENDANCE FOREIGN KEY (moduleID) REFERENCES module(moduleID));
insert into ATTENDANCE values ('F1','A1','E1','Yes','Yes','Yes','Yes','Yes','Yes'); insert into ATTENDANCE values ('F2','A2','E2','Yes','No','Yes','Yes','No','Yes'); insert into ATTENDANCE values ('F3','A3','E2','Yes','Yes','Yes','No','Yes','Yes'); insert into ATTENDANCE values ('F4','A4','E2','Yes','Yes','Yes','Yes','No','Yes'); insert into ATTENDANCE values ('F5','A5','E1''Yes','Yes','No','Yes','Yes','Yes'); insert into ATTENDANCE values ('F6','A6','E2','Yes','Yes','Yes','Yes','Yes','No'); insert into ATTENDANCE values ('F7','A7','E1','Yes','Yes','Yes','Yes','No','No'); insert into ATTENDANCE values ('F8','A8','E2','Yes','Yes','Yes','No','Yes','Yes'); insert into ATTENDANCE values ('F9','A9','E3','Yes','Yes','No','Yes','Yes','Yes');
CREATE TABLE MODULE (ModuleID char(4) NOT NULL, ATTENDANCEID char(4) NOT NULL, ModuleName varchar(45), ModuleCode varchar(6), PRIMARY KEY (ModuleID));
ALTER TABLE MODULE ADD FOREIGN KEY (attendanceID) REFERENCES attendance(attendanceID);
insert into MODULE values ('E1','F1','Workshop Research and Project Implementation','Im3023'); insert into MODULE values ('E2','F2','Advanced Database Development','SD3042'); insert into MODULE values ('E3','F3','Advacned Information Systems Develpment','SD3043');
