How To Alter Table Drop Constraint @variable?

Jun 12, 2003

I need to run the alter table to drop a default. However, the default name is kind of 'dynamic' from around 1000 databases, thus I need to run the following sql to get the name to a variable. Now, it looks the alter table statement does not like to drop a vaiable, is there a solution about it?

declare @radius_default varchar(40)
select @radius_default = (select sobj.name from sysobjects sobj
inner join syscolumns scolumn
on sobj.ID = scolumn.cdefault
where scolumn.name = 'radius' and sobj.name like '%LandMarks%')
print 'Need To Drop @radius_default: ' + @radius_default
--====================================
alter table LandMarks drop constraint @radius_default

thanks
David

View 1 Replies


ADVERTISEMENT

Alter Table Add Constraint Null Value

Dec 1, 2007

I have a stored procedure that adds constraints from a variable:
ALTER procedure [dbo].[addMyConst](@txtAuto_ourlim money OUTPUT,@txtGen_ourlim money OUTPUT)ASEXECUTE('ALTER TABLE [dbo].[tbl1] ADD CONSTRAINT DF_auto_ourlim DEFAULT ' + @txtAuto_ourlim + ' FOR Auto_ourlim')EXECUTE('ALTER TABLE [dbo].[tbl1] ADD CONSTRAINT DF_Gen_ourlim DEFAULT ' + @txtGen_ourlim + ' FOR Gen_ourlim')
This works fine unless one of the variables is null or empty. Then I get the  error: "Incorrect syntax near the keyword 'FOR'
Evidendly SQL Server Express sees "....DEFAULT + + FOR..." but I don't know.
I've fiddled with this a long time and haven't a clue how to fix it. Any help would be appreciated. Thanks, Steve

View 2 Replies View Related

Alter A Default Constraint In A Table

Jun 12, 2007

I have a default constratint on DateColumn getdate()-1

I have used enterprise manager to update it to yesterday's date everyday.

I would like to have a SQL which can check for the date in the system
or even a trigger which checks when the date changes the constraint is updated itself. If this is not possible I would like to have a stored procedure which I will schedule to run as a job everyday once.

So if today 6/12/2006, the default value in the Datecolumn should be
6/11/2006.

This gives me a error, i tried but could not fix the bug.

Alter Table TABLE_NAME
Alter Constraint DF_DATECOLUMN
Default getdate()-1

Ashley Rhodes

View 4 Replies View Related

Transact SQL :: How To Alter Table To Add A Constraint

Sep 19, 2015

There is a table exists in a database name tbDatabaseProviders.

I want to alter this table to add a column DatabaseProvider Also I want to add a default constraint so if nothing than 1 should be added.  

Remember, the table already exists so when I write the script query then I want to first check the constraint if it exists then I dont want to add the constrait otherwise I will add it.  

I was having problem to first add a constraint with the constraint name so I could find the constraint before finding it if it already exists. 

What should I do the above. 

View 3 Replies View Related

Using Alter Table To Drop Columns

Nov 5, 2000

I am dropping 60 out of 133 columns on a user table, but am finding that the spaceused by the table ( as shown by sysindexes.dpages ) is not being freed up.
I have dropped and recreated all indexes on the table, with no effect on the dpages value, as well as running dbcc updateusage for the table.
Is it not possible to free up the space used by using this method ?
Is recreating and repopulating the table with only the required columns the correct / only way to do this ?

thanks,
manoj

View 1 Replies View Related

ALTER TABLE APLLYING IDENTITY CONSTRAINT

Apr 9, 2008

Pls help me

i want to alter table and add identinty constraint on one column

create table play8(id int)
" alter table play8
alter column id int identity(1,1) " GIVES ERROR or it is posibble


Yaman

View 1 Replies View Related

Alter Table Nocheck Constraint Still Some Dependencies

May 17, 2006

Hi.I'm getting errors like this when I try to run an upgrade script I'm trying towrite/test:altering labels to length 60Server: Msg 5074, Level 16, State 4, Line 5The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.I used this to bracket my script:sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"gosp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"go/* updates here */sp_msforeachtable @command1="print '?'",@command2="ALTER TABLE ? CHECK CONSTRAINT all"gosp_msforeachtable @command1="print '?'",@command2="ALTER TABLE ? ENABLE TRIGGER all"goI guess the alter table nocheck constraint isn't disabling the fk'scompletely?Is there a way around this, or do I manually have to do the constraintdropping/recreating?ThanksJeff Kish

View 3 Replies View Related

Alter Table Drop Column Error

Apr 11, 2001

1. We have user defined type (Sql type = bit ) with rule setting it to 0
2. New column(isVIP) was created using this user defined type
3. sp_unbindrule was used to unbind rule from new column (isVIP)
4. Alter table Client drop Column isVIP !!!! (invalid syntax )

Any ideas ?
Thanks

View 4 Replies View Related

Is There A Alter Table Xxxx Drop Default Yyy ?

May 11, 2005

Hi:
I need to change a column's datatype from tinyint to int as follows:
alter table tableName
alter column column1 int

but with error <<'DF__LandMarks__color__6A50C1DA' is depending on it.>>

However, this old default is not part of the constraint. Thus, the only way is to delete it from sysobjects.

unfortunately, the following code I have to commented since they could only be executed line by line, not within a begin...end block.
--exec master.dbo.sp_configure 'allow updates', 1
--reconfigure with override
--delete from sysobjects
-- where name = 'DF__LandMarks__color__6A50C1DA'
-- and type = 'D'
--exec master.dbo.sp_configure 'allow updates', 0
--reconfigure with override

I need to update 10 server around 2500 databases with this change.
I have looked INFORMATION_SCHEMA related views, but not found default related, maybe I missed something.

thanks
David

View 4 Replies View Related

ALTER TABLE DROP COLUMN LastUpdated Failed Because One Or More Objects Access This Column.

Mar 7, 2008

Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'.
ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
 

View 1 Replies View Related

Can't Drop Constraint...?

Aug 30, 2006

Hello, I have hit the wall here...can't make sense of this one.

I have a script that creates a PRIMARY KEY constraint called PK_tblDRG
CODE:

ALTER TABLE [dbo].[tblDRG]
ALTER COLUMN [record_id] Uniqueidentifier NOT NULL
Go
ALTER TABLE [dbo].[tblDRG]
WITH NOCHECK ADD PK_tblDRG PRIMARY KEY CLUSTERED
(
[record_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]

All is fine with that. I run this to verify:

EXEC sp_pkeys @table_name = 'tblDRG'
,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'

which returns this:

TABLE_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEKEY_SEQPK_NAME
Relational_05Q3dbotblDRGrecord_id1PK_tblDRG

Now I want to drop the constriant if it exists:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK__tblDRG]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG

AND I get this in return:

The command(s) completed successfully.

So, lets double check:

EXEC sp_pkeys @table_name = 'tblDRG'
,@table_owner = 'dbo' ,@table_qualifier = 'Relational_05Q3'

AND I STILL GET THIS:

TABLE_QUALIFIERTABLE_OWNERTABLE_NAMECOLUMN_NAMEKEY_SEQPK_NAME
Relational_05Q3dbotblDRGrecord_id1PK_tblDRG

Hmmmmm. Looks like the IF statement didn't do it's job. Ok fine. I'll just kill it myself:

ALTER TABLE [dbo].[tblDRG] DROP CONSTRAINT PK__tblDRG

AND I GET THIS?!?!?!?!

Server: Msg 3728, Level 16, State 1, Line 1
'PK__tblDRG' is not a constraint.

What am I not getting here? Is it me...I can take If I am a bone head here.

Any help would be appreciated. Thanks!

View 1 Replies View Related

Cannot Alter Or Drop Fields

May 15, 2006

I must change replication schema from:
Publisher - SQL2000(8.0.2039), Disrtibuter/Subscriber - SQL2000
to:
Publisher - SQL2000(8.0.2039)(The same server), Disrtibuter/Subscriber - SQL2005(9.0.2047)(new server)

I have removed all subscription, publications, distributer and publisher. So there should not be now any information about replication.

Now I want to make some changes with tables and i try to alter and drop columns on publisher database. But it wont do... I always receive messages that tables and fields are being used in replication:

ALTER TABLE Clients DROP COLUMN ABCCode
Msg 4932, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN failed because 'ABCCode' is currently replicated.

I tried to use sp_MSunmarkreplinfo and sp_removedbreplication but it did not help. I also tried to drop column with sp_repldropcolumn but got this message: Invalid object name 'sysmergearticles'.
Looks like server (publisher) still has information about old replication. But how to delete this information? And how can I alter and drop fields?

Thanks

View 4 Replies View Related

Drop And Reacreate CONSTRAINT

Oct 9, 2001

ALTER TABLE doc_exe ADD

/* Add a PRIMARY KEY identity column. */
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,

/* Add a column referencing another column in the same table. */
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a)

go

ALTER TABLE doc_exe
DROP CONSTRAINT column_c_fk

How to recreate CONSTRAINT column_c_fk without droping and reacreating column column_c or add new CONSTRAINT on column column_c ?

Thanks a lot .

View 3 Replies View Related

Drop Constraint Never Finishes

Oct 13, 2002

I need to make some changes to tables in a database. To do this requires that I "alter table" and "drop constraint" All constraints drop properly, except one. The last one will churn away forever in query analyzer and never drop. I am unable to make the necessary changes until this completes. I am guessing that there is a data issue that is keeping the constraint from being dropped. Any ideas on how to proceed? Thanks!

View 2 Replies View Related

Drop Column With Constraint

Sep 25, 2007



Hello !

I am using Microsoft SQL Server 2000

I am trying to drop a column that has a constraint, executing the script inside a transaction:

BEGIN TRANSACTION

ALTER TABLE MOPTeste DROP CONSTRAINT FK_IDMOPPais


ALTER TABLE MOPTeste DROP COLUMN IDMOPPais

COMMIT

If i dont commit the drop constraint, it wont let me drop the column

Solutions?



View 3 Replies View Related

Alter Column With Default Constraint

Dec 7, 2001

I can't seem to get the syntax correct for ALTERing an existing column with a default constraint. I've been to Help and BOL. There are examples that show how to use the ALTER command to add a column with a default constraint but not how to do it on an existing column.

Any help would be appreciated.

Sidney Ives

View 1 Replies View Related

Why Variables In A Alter Or Drop Does Not Work.

Oct 26, 1999

I am writing a procedure to drop a specific user's schema only. I select all his/her table name and foreign key in a temp table first and then use a cursor to get the table name and foreign key, and begin drop foreign keys and drop table. However, sql think it is wrong with the syntax like "alter @table_name". Does anyone have a clue, what is wrong with my syntax. Or someone know how to drop one specific user's schema only (Other user may have same table in the database, and you don't want to drop that.

Attached is my scripts: please correct it. Thanks.

CREATE procedure sp_drop_schema @user sysname
as

select o.name table_names, o.id table_id, s.parent_obj Key_id, s.name key_name, u.name users_name
into #temp1
from sysobjects o inner
join sysobjects s on o.id=s.parent_obj
join sysusers u on o.uid=u.uid
where s.type='f' and u.name=@user

declare cur_f cursor
for select table_names, key_name

declare @table_name varchar (80), @key_name varchar (80)

open cur_f

fetch next from cur_f into @table_name, @key_name
while @@fetch_status=0 begin
alter table @table_name
drop constraint @key_name
fetch next from cur_f into @table_name, @key_name
end
close cur_f
deallocate cur_f
drop table #temp1

select o.name table_name, o.id table_id, o.uid users_id, u.name users_name
into #temp2
from sysobjects o
inner join sysusers u on o.uid=u.uid
where type='u' and u.name=@user

declare cur_temp cursor
for
select table_name
from #temp2

declare @tab_name varchar (80)
open cur_temp
fetch next from cur_temp into @tab_name
while @@fetch_status=0 begin
drop table @tab_name
fetch next from cur_temp into @tab_name
end
close cur_temp
deallocate cur_temp
drop table #temp2

View 1 Replies View Related

Thoughts On ALTER Vs. DROP & CREATE.

Nov 8, 2006

Thought I should post in the newbie forum for a while, instead. :-)

I have a couple of scripts that I've generated that drop a couple of system stored procedures and recreate them. I'm not sure why I did it in the first place, but I think it was that it wouldn't let me run an ALTER statement on them. Specifically, I'm now looking at sp_add_operator. I changed it to a 500 character email field instead of whatever it was (100, I think.)

/* Explanation: Why did I do that? SQL Mail is prohibited here, so I'm using CDO_Sysmail to email myself and the developers if a job fails. The list of people to email is determined by the owner of the database, who is also an operator in SQL. I get the list of emails from the email field of the operator properties. Hence, I need a bigger email field. Yes, I now know it would most likely be better to create an ADMIN database on each server for this kind of stuff. (Thanks to Tara for that blogged suggestion.) */

While I will probably go back to the default stored procedure, this got me to thinking: when would it be better to use an ALTER statement on a SProc rather than to do a DROP and CREATE?

Your thoughts, oh SQL gurus?

View 12 Replies View Related

When Do I Need To Drop Column Statistics When Doing An Alter?

Aug 30, 2007



I need to know definitively when I need to drop and recreate column statistics (histogram) when altering a column. Empirically, it seems I can lengthen a varchar or change a not null column to a nullable one, and then existing statistics dont' cause


Msg 5074, Level 16, State 1, Line 1

The statistics 'c1' is dependent on column 'c1'.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN c1 failed because one or more objects access this column.


But, if I change a column to not nullable or shorten a column, I get the error.

Is this the complete description of when I need to drop and recreate column statistics and when I don't?

View 3 Replies View Related

Alter Column Drop Identity

Apr 18, 2008

Is there any way to remove the IDENTITY property of a column? I originally used it, but it caused so many headaches with ADO.NET that I've decided to just increment the darn things manually. So I want to turn it into just a normal integer column.

If there's no direct way, can someone suggest a quick way to create a new column, copy all the old values into the new column, change the primary key to the new column, and drop the old column?

Thanks.

View 3 Replies View Related

Urgent!drop Foreign Key Constraint

Mar 1, 2001

I have to drop foreign key constraint from one table. How can I do that? Is it possible?
Also,

Is it possible to add a foreign key constraint on the table once it has a data?

View 1 Replies View Related

How To Drop UNIQUE CONSTRAINT On Only One Column

May 5, 2014

I am very new to sql and I have got this doubt.

CREATE TABLE employee
{ EMP-ID VARCHAR UNIQUE,
DESIG VARCHAR UNIQUE,
SALARY INT };

Probably this is worst table ever created :).Now How to drop UNIQUE constraint on only column lets DESIG.

if i write

ALTER TABLE employee
DROP CONSTRAINT myuniqueconstraint;

will drop constraints on both columns which I dont want.

Any query that remove UNIQUE CONSTRAINT only on DESIGN column.

and one more clarification,

ALTER TABLE employee
MODIFY SALARY INT NOT NULL;

ALTER TABLE employee
ALTER COLUMN SALARY INT NOT NULL;

which of the above two is right query to add NOT NULL constraint to the above employee TABLE?

View 1 Replies View Related

Drop Primary Key Constraint Of (#) Hash

Jul 20, 2005

Hi there,I have created a hash table. After using it, somehow the primary keyconstraint of this hash table still exist in database. Which causeerror.When I delete this constraint with Alter table Drop con....It gives no table exist error.Can anybody give any idea.Thanks in Adv.,T.S.Negi

View 2 Replies View Related

UNABLE To ADD Or DROP A Constraint - SQL Complains!

Apr 21, 2008

Hi all, I am trying to create a CONSTRAINT but for some reason T-SQL does not allow me to do that.

When I try to DROP a CONSTRAINT it says:

Msg 3728, Level 16, State 1, Line 13'DF_TBL_SyncTable_DEVUK' is not a constraint.Msg 3727, Level 16, State 0, Line 13Could not drop constraint. See previous errors.

When I try to ADD a CONSTRAINT it says:
Msg 1781, Level 16, State 1, Line 14Column already has a DEFAULT bound to it.Msg 1750, Level 16, State 0, Line 14Could not create constraint. See previous errors.


For some reason I can't win here. Can't drop it nor can I create one. Any solution?




Code Snippet
ALTER TABLE TBL_SyncTable DROP CONSTRAINT DF_TBL_SyncTable_DEVUK
ALTER TABLE TBL_SyncTable ADD CONSTRAINT GOD_TBL_SyncTable_DEVUK DEFAULT 2 FOR DEVUK


Thanks for comments + suggestions.

*UPDATE*

I am trying to use the following code to check if the SCHEMA exists but still no luck. For some reason when I create it and wrap an IF statement around it, it doesn't detect the SCHEMA. Is something wrong with my code?






Code Snippet

IF EXISTS(

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'dbo'


AND CONSTRAINT_NAME = 'DF_TBL_SyncTable_DEVUK'


AND TABLE_NAME = 'TBL_SyncTable'

)
SELECT * FROM TABLE_1
ELSE
ALTER TABLE TBL_SyncTable ADD CONSTRAINT DF_TBL_SyncTable_DEVUK DEFAULT 2 FOR DEVUK

View 4 Replies View Related

Alter Column Datatype With Default Constraint

Mar 16, 2004

I need to alter the datatype of a column from smallint to decimal (14,2) but the column was originally created with the following:

alter my_table
add col_1 smallintNot Null
constraint df_my_table__col_1 default 0
go

I want to keep the default constraint, but i get errors when I try to do the following to alter the datatype:

alter table my_table
alter column col_1 decimal(14,2)Not Null
go

Do I need to drop the constraint before I alter the column and then rebuild the constraint? An example would be helpful.

Thx

View 1 Replies View Related

SQL 2012 :: How To Drop Columns From Alter View

Mar 20, 2015

I have a VIEW which is dynamically generated through complex dynamic SQL. Unfortunately the dynamic SQL uses "Select * from table" to select the columns because the programmer did that to reduce the amount of code in the dynamic SQL string as the code can't be debugged if it's too long.

Therefore, I have a VIEW with columns in it I don't need, and want to remove them from the view - I need to remove all columns with column names matching the syntax '%1%_2' .

The view is called TEMP_EXPORT_1

I can either use the code below to return a list of columns that I want removed:

select column_name from information_schema.columns

where table_name='TEMP_EXPORT_1' and column_name like '%1%_2'

Or I can use the code below to return the list of columns that I want to keep:

select column_name from information_schema.columns

where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2'

Now how would I go about altering TEMP_EXPORT_1 view so that it no longer has these columns? I know views don't have a drop statement...

Therefore I tried the following but I'm not sure of the syntax:

ALTER VIEW dbo.TEMP_EXPORT_1
AS
SELECT (select column_name from information_schema.columns
where table_name='TEMP_EXPORT_1' and column_name not like '%1%_2')
FROM dbo.TEMP_EXPORT_1

Am I on the right track? how can I ALTER this view to remove these columns? ... I want to keep this separate from the code that generated the view as I want it as an optional procedure that can be run if needed.

View 2 Replies View Related

Drop Create Vs Alter In Regards To Stored Procedures

May 17, 2007

Anyone have any compelling arguements for using one over the other?



Thanks



Mercy

View 7 Replies View Related

Whats Wrong Here, Drop Constraint Primary Key ? Help Pls...

Jun 14, 2000

select * from sysobjects where type = 'K'

name
------
pk_dtproperties
1 row(s) affected)

When I say - DROP CONSTRAINT pk_dtproperties

i get this error
-------------------
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONSTRAINT'.

NOTE:

In tables, I don't have any tables all tables i deleted but wonder from where this creature comes from,.........

View 2 Replies View Related

SQL Security :: Deny Alter And Drop Permissions On ONLY Stored Procedures

Aug 19, 2015

We have a generic sql login "prduser". Applications use this login. We want the login NOT to have ALTER PROCEDURE and DROP PROCEDURE permissions only on the stored procedures(there are thousands of them).

View 17 Replies View Related

Database Diagrams: FOREIGN KEY Constraint Fell Off; Can't Drop/recreate It...

Mar 25, 2006

Hello,

I have two tables Person & Location where Location has a primary key LocationId and Person has a foreign key LocationId.

Sometime ago I used the Database Diagrams visual tool of SQL Server Management Studio Express to create the foreign key relationship between the two tables - i.e. "visually" (drawing a line between the PK & FK LocationId elements of both tables).

Time has passed and I recently noticed that, upon retrieving my saved diagram, the foreign key relationship had "fallen off" (i.e. the many-to-one line was no longer showing in the diagram).

After recreating the relationship (redrawing the line) I find that I get an error message when I try to save the diagram:

Post-Save Notifications
[!] Errors were encountered during the save process. Some database objects were not saved.

'Location' table saved successfully
'Person' table
- Unable to create relationship 'FK_Person_Location'. 
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Person_Location". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

When I go back to the object explorer and view the dependencies for the two tables, there is no dependency (between these two tables) revealed.  When I try to create the foreign key constraint manually (T-SQL) it again says can't add the constraint.  It comes up with an error as follows:

ALTER TABLE Person
ADD FOREIGN KEY (LocationId) REFERENCES Location (LocationId)

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__Person__LocationId__793DFFAF". The conflict occurred in database "mydb", table "dbo.Location", column 'LocationId'.

(Note:  Each time I do this, the 8 hexadecimal character suffix changes.)

When I try to drop the foreign key:

alter table Person
drop constraint FK__Person__LocationId

it comes back with the error:

Msg 3728, Level 16, State 1, Line 2
'FK__Person__LocationId' is not a constraint.
Msg 3727, Level 16, State 0, Line 2
Could not drop constraint. See previous errors.

So it seems that there's some kind of goof up here.  Can anybody shed light on this / tell me how to fix it?

View 6 Replies View Related

Alter Table Alter Column In MSACCESS. How Can I Do It For A Decimal Field?

Jul 23, 2005

Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Drop All Indexes In A Table, How To Drop All For User Tables In Database

Oct 9, 2006

Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob

View 2 Replies View Related

Alter Table Alter Column

Jul 20, 2005

I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved