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
ADVERTISEMENT
May 17, 2007
Anyone have any compelling arguements for using one over the other?
Thanks
Mercy
View 7 Replies
View Related
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
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
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
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
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
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
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
View Related
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
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
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
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
Nov 13, 2007
I want to use Create a procedure if not exisit and Alter it if exisit.
Can I use Create and Alter keyword together while writing a procedure in SQL Server 2005? If YES than HOW?
View 2 Replies
View Related
Jun 5, 2008
Hi
I am wana to know why we every time drop the procedure and again create it as below...will that impact on performance or compilation of SP???
Use Northwind
GO
IF OBJECT_ID('dbo.ListCustomersByCity') IS NOT NULL
DROP PROC dbo.ListCustomersByCity
GO
CREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%'
AS
SELECT City, COUNT(*) AS NumberOfCustomers
FROM Customers
WHERE Country LIKE @Country
GROUP BY City
GO
T.I.A
View 4 Replies
View Related
Apr 19, 2006
I am new to writing SQL code and I read that you can use ALTER statements to create an index for a table. How would I go about doing that? Everything that I have tried in Query Analyzer comes up with an error.
Any help is appreciated.
View 3 Replies
View Related
May 11, 2007
when i create a new stored proc, i say "CREATE PROCEDURE [sprocName]"
but the next time i edit the stored proc, it hasnt changed to "ALTER ..."
I manually change it to ALTER, and run it, and close it, and it reverts back to "CREATE..."
Is there some sort of privelage i have to enable? Has anyone encountered this before?
Help guys, please :-S
Also, if you know of a better place where i can post this thread, please let me know
View 3 Replies
View Related
Jul 27, 2006
What kind of problems may I see if a process drop and re-create a set of tables every night?
View 1 Replies
View Related
Oct 19, 2001
Hello,
What's the command for dropping an existing Foreign key on a table and reacreating it in SQL Server 6.5
Thanks
Sri
View 1 Replies
View Related
Mar 17, 2000
Hi,
I had a question and did not obtain an answer. So I am trying to rephrase and ask again, in case I was not clear the first time.
When we drop and recreate a table, do we always have to recompile the stored procedures that reference the table? Or is only under certain scenarios that we need to do so- like if an index on the table is changed
Please let me know
Thanks in advance
Kiran
View 1 Replies
View Related
Feb 4, 2005
I have to run a Big Sproc for make a lot of updates and insert. because trigger it take to many time.
I can drop the trigger before the procedure and recreate it after, but I wondered whether there existed of other solution?
Can I deactive the trigger? I'm affraid too got two copie of code for the trigger that why I dont really like the Drop-Create solution...
Thanks
View 3 Replies
View Related
Feb 20, 2005
Hi all,
I have 2 questions:
1. How can I drop /remove a complete db from MSDE desktop engine?
2. After exporting my db onto MSDE server and get connected those db tables in FE (adp), how can I create a new table in the same back end db?
I know these are basic questions but since I am new to MSDE I hope the forum would bear me.
With kind regards,
Ashfaque
View 5 Replies
View Related
Feb 15, 2004
Hi,
How can i create and drop table in MS SQL Server 2000 via VB6? I think I should use ADOX object, but I don't know exactly how....
The following code uses ADO connection object and returns with runtime error "incorrect syntax near AS":
Dim db as ADODB.Connection
'... open connection to database
Dim strCmd As String
strCmd = "CREATE TABLE tmp_tbl AS SELECT * FROM tbl"
db.Execute strCmd
Thank you in advance
View 1 Replies
View Related
Feb 16, 2007
Hi, all,
I want to re-create an index on a production table.
I got an error 644 "could not find index entry...".
The DBCC CHECKDB and CHECKTABLE gave me this:
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 37575172, index ID 6: Page (1:939782) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 37575172, index ID 6, page (1:939782). Test (*(((int*) &m_reservedB) + i) == 0) failed. Values are 7 and 36.
DBCC results for 'Mfg_DFSFNSF'.
There are 1142314 rows in 326143 pages for object 'Mfg_DFSFNSF'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table 'Mfg_DFSFNSF' (object ID 37575172).
The table script for the index is like this:
CREATE UNIQUE INDEX [Mfg_ITMDH_MbrIdx] ON [dbo].[Mfg_DFSFNSF]([_ITMDH_OwnRow], [_ITMDH_MbrKey], [RECTYPE]) WITH FILLFACTOR = 70 ON [PRIMARY]
My question is that can I drop it and run above create it to fix the problem in live mode?
I know the other option will be:
DBCC CHECKTABLE (FSDBMR.dbo.Mfg_DFSFNSF, repair_allow_data_loss)
But that has to put the db under single user mode.
Thanks!
View 6 Replies
View Related
Jul 18, 2006
Here is the alter statement that I am trying to use to create a relationship between 2 tables. This does not seem to work on mobile. What am I doing wrong?
ALTER TABLE [SubCategory] CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID])
REFERENCES [Category] ([CategoryID])
ON UPDATE CASCADE
ON DELETE CASCADE
View 3 Replies
View Related
Oct 10, 2006
Hi What is the SQL syntax to create and destroy a stored proceedure with t-sql? Is there any good tutorials?ThanksPWP
View 5 Replies
View Related
Nov 7, 2000
If I rebuild the clustered index using this option (Drop Existing) without specifying the Fill Factor, would it it retain the original Fill Factor?
Thank you
View 2 Replies
View Related
Oct 2, 2000
How to SQL server save information about create/drop objects action. How can I get this? Example for, a lot of objects(sp) in my database has been dropped, how I know who was dropped them (user login & time)?
View 1 Replies
View Related
Mar 19, 2008
Hi All,
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name]
Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
Thanks,
Srinivas
View 3 Replies
View Related
Jan 31, 2008
Hi,
I increased one of my base tables column which is referenced in view
I noticed sql server didn't recognized this change and its still showing old field size in the view.
I can simply drop and create it again. But wanted to know if there is any way (command/sp) to recompile the view which will be easy to deploy in production as patch.
Thanks
- D
View 11 Replies
View Related
Mar 19, 2008
Hi All,
I am Srinivas working on SSAS 2005. I am not able to create or drop dimension members. I need to drop and create all members in one dimension before cube processing, as the columns are coming dynamically.
E.g. ALTER CUBE [Reporting] DROP DIMENSION MEMBER [Tbl Analysis Test].[Name]
Here cube is [Reporting] , dimension is [Tbl Analysis Test] and member is [Name].
Its giving the following error " Parsing the query ... Query (1, 46) The member '[Name]' was not found in the cube when the string, [Tbl Analysis Test].[Name], was parsed. Parsing complete"
Please look into this issue, advice me what do I do ASAP.
Thanks,
Srinivas
View 1 Replies
View Related
Apr 12, 2006
I have a package that i want to move between enviroments. Therefor i need to create a package that creates all my tables on the new server.
like
-- "if exists (select * from dbo.sysobjects where id = object_id(N'[table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [table]
GO"
The case is that i have around 30 tables, and my question is therefore
I know that you can create the drop and create script for one table at the time in the SQL Server Management Studio, but is there an easier way to do this
View 1 Replies
View Related
Feb 5, 2015
Without giving dbowner how can we grant permission to user to alter/create procs?
View 3 Replies
View Related
