Alter Tables
Apr 20, 2006Dear friends
I would like to alter columns so that I have have empty string records instead of null. How do I query all tables and all columns so I can alter the column properties programmatically?
Thanks
Cm
ADVERTISEMENT
Alter All Tables Script
May 18, 2006So I needed to add 2 columns to about 20 tables. Not much and I could have easily wrote a script for each table. But thats boring. So I started searching around.
I found up with this from this post http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65351
Select 'Alter table '+table_name+' Add yourcol datatype' from information_schema.tables
where table_name<>'dtProperties'
Cool but I wanted it to run and execute at once, not just create the scripts. The only way I could think was is with a cursor. Something I didn't want to do. But did anyway. I came up with this.
DECLARE Alter_tables_cursor CURSOR
FOR
select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
PRINT 'Alter table '+@tablename+' Add ModifiedBy int, ModifiedOn datetime'
EXEC('Alter table '+@tablename+' Add ModifiedBy int, ModifiedOn datetime')
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
END
PRINT 'All user-defined tables have been Altered.'
DEALLOCATE Alter_tables_cursor
I'm sure there are better ways. If so post them.
http://www.jiltedcitizen.com
Need To Alter Data Type On All Tables..
Jul 17, 2003I need to alter datatypes from
numeric(18,0) to 'int' type across all the tables in the database of about 200 tables. Nearly 200 PK/FKs will be affected with this change..How can I do this with a script? Can anyone help witha script which drops all the constraints/indexs etc and then alters the datatype before re-creating the constraints/indexs..?
P.s. No rounding problem is expected as data is already in whole numbers.
Thanks,
Di.
Alter Index For All Tables/indexes In A DB
Oct 22, 2007
In SQL Server 2000 one could DBReindex every index that exists in a given database. You can do the same in SQL Server 2005. But how can this be done with the new Alter Index command? It does not allow me to pass in a variable for the object. Any ideas on how to get this done in with Alter Index in 2005? Thanks!
This I can't get to work:
DECLARE
@TableName nvarchar(100)
SET @TableName = 'Account'
USE database;
GO
ALTER INDEX ALL ON @TableName
REBUILD
GO
USE RZTQ5OL02
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT Name from sys.sysobjects where type = 'U'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
User That Can Alter And Modify Tables
Feb 7, 2008What do I have to do in order to create a user that can alter and modify some/all tables?
Thank you.
Hi Greg , Any Solution For Alter Tables On Publisher And Subscriber End As Well ....?
Dec 11, 2006Hi Greg Y and seniors ones,
I am working with replication on sql server 2005 (standard edition sp1).There is scenario that some time one of the team of coders want to alter objects mostly tables being replicated on publication database but unable to do that due to error on adding column "Cannot add columns to table 'table1' because it is being published for merge replication.." in sql server 2000.
While other one want to alter replicated objects on subscriber end (like name of objects, add columns in replicated table etc).
We was working on sql server 2000 and for implementing this scenario I always use mechanism disabling/reconfigure the replication setup by the mean of long exercise.
After that, In order to alter the objects in publication database simple DDL script was executed after disabling the replication.
While manipulating the requirements on subscriber end, I created tables with same structure as replicated tables and replicate the data on self created tables by customizing code in triggers (ins_C9D57350-605A-4D87-85C0-0DB645F1CEC8 etc.) of replicated tables.
Also I have script of all replicated tables€˜s triggers but when I rerun snapshot agent it replaces the name of triggers with new one so at that time I lost my mind and I put code again in all tables€™ triggers. IS there any way to force sql server 2000/2005 generate and rerun snapshot but use already generated guid of articles instead created new one .
Let me know Plz, is there any solution/feature or any easy way in sql server 2005 to avoid this annoy exercised. I could implement this scenario.
Alter Table Alter Column In MSACCESS. How Can I Do It For A Decimal Field?
Jul 23, 2005Hi 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 RelatedIs There A Tool (or Script) That Will Compare 2 Tables And Create A Script To Alter The First To Match The Second?
Dec 30, 2004Does that make sense? I have 2 tables, one is an older version, one newer. The newer version has some additional fields, indexes, etc. I'd love to have a tool to run against them that would generate an ALTER TABLE script to modify the old table so that it matches the new table.
Is there anything like that out there?
Alter Table Alter Column
Jul 20, 2005I 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 RelatedAlter Table Alter Column...
Oct 8, 2007I am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?
my code:
Alter table customers alter column age
TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column
Sep 7, 2007Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
Why Would Tables Pulled In From ODBC In Access Be Different Than Tables In SQL 2005 Tables?
Jan 24, 2008I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
ALTER
Jun 23, 2000I have SQL Server 7.0 running on both development and production boxes. The syntax below runs fine on my development box, but I am getting an error on my production box. Thanks for your help
ALTER TABLE SUPPORTINFO
ALTER COLUMN STORENUMBER VARCHAR(20)
Error Message:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'COLUMN'.
Alter SP From SP
Apr 5, 2007Hello everyone.
I want to make a stored procedure that alters another stored procedure.
Is there a way to do it?
I need to to be able to execute a string as sql:
Something like
execute statement 'ALTER PROCEDURE ....'
ALTER ?
Nov 13, 2007What is the ALTER statement for ?
for example in some stored procedures
you have
ALTER PROCEDURE (procedure name..)
Need To Alter A DML Trigger
May 17, 2007Hi
I'm trying to write a trigger to insert data into an archive file. I added a new trigger using database explorer, wrote the trigger and then saved it. The trigger has an error in it and I need to alter it. Can you tell me how to access the trigger ?
Many thanks
Chris
Alter 'sa' Login
May 22, 2008In my SQL SERVER 2005, sa login is enabled, despite Windows Authentication mode being set.
When I'm trying to change it to disabled, I get the error "cannot alter 'sa' login. it doesn't exist or you don't have the permission"
How can I change the status?
SQL Alter Collation Name
Dec 3, 2004Hello:
I have a database in SQL with the following collate name: SQL_Latin1_General_CP1_CI_AS... I am trying to change the accent sensetive to accent insensitive... how would I do this? I tried re-installing the SQL and setting the default to CI_AI, but since the database that is backed up uses CI_AS, the DB settings overrides the default settings...
Any suggestions?
Alter A Table All In C#
Feb 15, 2006Is there a way to add a column to an existing table and do it all in C#If my query string is as follows how do I execute the query?ALTER TABLE interests ADD COLUMN Swim VARCHAR(1) NOT NULL DEFAULT('n')ThanksMoonWa
View 2 Replies View RelatedNeed Someone Who Can Alter A Sql 7 Script For Me
Apr 2, 2002Hello,
I tried to do this myself, but couldn't figure it out since I am very unfamiliar with sql. I have a script that exports data from our store database to a file that is used to update our webstore database. I want to add a chunk to the script that will copy an image file from one directory to another as it loops through the database. I want to copy a picture from directory1 to directory2 that has a name SKU.jpg.
I was told in this forum by Ray Miao that the format of the statement would be:
xp_cmdshell 'Copy c:directory1SKU.jpg c:directory2SKU.jpg'
My problem is that I don't know how or where in my script to place this statement so that it will work. If this is simplke and someone can tell me the setup that would be great. If not, I would be willing to hire someone to do what needs to be done to this script and send it to me. I can be reached at darren@jbjgifts.com or 217-369-2686.
Here is the script:
-------------------------------------------------------------------
use TAMDATA
go
set NOCOUNT on
/* GET SKUS THAT ARE NOT ON SALE HERE */
select s.sku SKU,
s.Description Description,
CONVERT(DECIMAL(9,2),s.SURetail) Price,
null Weight,
Replace(Replace(w.commentary,CHAR(13)+ CHAR(10),'<BR>'),Char(39),'') MarketingDescription,
'Yes' Taxable,
v.Company+' '+c.description SoftCartCategory,
null SoftCartTemplate,
/*lower('template_'+REPLACE(v.company,' ','_')) SoftCartTemplate,*/
null VendorNo,
null ListPrice,
null Graphic,
null Thumbnail,
null SoftCartAttributes,
ISNULL(s.vendstock,'none listed') vin,
/*CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits-2)) InventoryQuantity,*/
CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits)) InventoryQuantity,
v.company vn,
cl.description cl,
d.description dt,
c.description ct,
ISNULL(sz.description,'none listed') Size1,
ISNULL(clr.description,'none listed') Color,
' ' OnSale,
'N' OnSaleNow
from SKU S,
VENDORS V,
CATEGORIES C,
DEPARTMENTS D,
CLASS CL,
SKU_LOCATION SL,
SKU_WEB_INFO W,
SIZES SZ,
COLORS CLR
wheres.skuid=sl.skuid and
s.skuid=w.skuid and
s.vendorid=v.vendorid and
s.categoryid=c.categoryid and
s.deptid=d.deptid and
s.skuid=sl.skuid and
s.classid*=cl.classid and /* May not be a Class Outer Join */
s.size1id*=sz.sizeid and /* May not be a Size Outer Join - presume only first size is used */
s.colorid*=clr.colorid and /* May not be a Color Outer Join */
/*(sl.OHUnits-sl.PRUnits-sl.LYUnits-2)>0 and */ /* available must be > 2 */
(sl.OHUnits-sl.PRUnits-sl.LYUnits)>0 and
w.PublishToWeb=1 and
sl.location=1 and /* JBJ has only 1 Location */
(s.SalePrice=0 or s.saleprice is null
or getdate() < s.SaleStartDt or getdate() > s.SaleEndDt )
Union /* Pulls the two result sets together */
/* GET SKUS THAT ARE ON SALE HERE */
select s.sku SKU,
s.Description Description,
CONVERT(DECIMAL(9,2),s.SalePrice) Price,
null Weight,
Replace(Replace(w.commentary,CHAR(13)+ CHAR(10),'<BR>'),Char(39),'') MarketingDescription,
'Yes' Taxable,
v.Company+' '+c.description SoftCartCategory,
null SoftCartTemplate,
null VendorNo,
null ListPrice,
null Graphic,
null Thumbnail,
null SoftCartAttributes,
ISNULL(s.vendstock,'none listed') vin,
/*CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits-2)) InventoryQuantity,*/
CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits)) InventoryQuantity,
v.company vn,
cl.description cl,
d.description dt,
c.description ct,
ISNULL(sz.description,'Not Applicable') Size1,
ISNULL(clr.description,'Not Applicable') Color,
'On Sale! Normally $'+CONVERT(VARCHAR(13),s.suretail) OnSale,
'Y' OnSaleNow
from SKU S,
VENDORS V,
CATEGORIES C,
DEPARTMENTS D,
CLASS CL,
SKU_LOCATION SL,
SKU_WEB_INFO W,
SIZES SZ,
COLORS CLR
wheres.skuid=sl.skuid and
s.skuid=w.skuid and
s.vendorid=v.vendorid and
s.categoryid=c.categoryid and
s.deptid=d.deptid and
s.skuid=sl.skuid and
s.classid*=cl.classid and /* May not be a Class Outer Join */
s.size1id*=sz.sizeid and /* May not be a Size Outer Join - presume only first size is used */
s.colorid*=clr.colorid and /* May not be a Color Outer Join */
/*(sl.OHUnits-sl.PRUnits-sl.LYUnits-2)>0 and /* available must be > 2 */*/
(sl.OHUnits-sl.PRUnits-sl.LYUnits)>0 and
w.PublishToWeb=1 and
sl.location=1 and /* JBJ has only 1 Location */
(s.SalePrice>0 or s.saleprice is not null) and
getdate() between s.SaleStartDt and SaleEndDt /* Presumes this export will be run daily be Darren */
go
--------------------------------------------------------------------
Thanks,
Darren
Alter Column
Jul 15, 2002Hi smart people!
I would like to know how to alter a column to have a default value. For instance I have a column AreaCode Char(3) in a table. I have data in the table and now I want to add a default value of '123' to the AreaCode column.
I tried the following but did not work.
Alter Table Phone
Alter Column AreaCode Char(3) Default '123'
Can we even do it using SQL?
Thanks
ALTER TABLE
Aug 13, 2002I think I have blown a gasket..... a elementary simple sp that will not work but I am unable to find an explanation as to why..... please help.
CREATE PROCEDUREbrsp_table_alteration
@var_1 varchar(45)
,@var_2varchar(45)
,@var_3varchar(30)
AS
ALTER TABLE @var_1
ADD@var_2@var_3NULL
Thanks.
Alter Column Name
Jul 12, 2001Is there any way to alter a column's name using Transact SQL and not the GUI interface?
View 1 Replies View RelatedAlter Statement
May 15, 2000Do anyone knows the syntax for changing the name of a column in a table with the
alter statement or any other statements????
Thanks in advance,
Vic
Alter Statement
Jan 5, 2001Hi
Is it possible to remove Identity property of a column using ALTER statement in SQL Server 7.0.
Thanks in advance
Rahul
Alter Database
Jan 23, 2001I am trying to move one of my database log to different location but I am geeting an error when I try to do this.
I am moving Registration DB log file from 'c:mssql7
egistrstion_log.ldf'
to 'e:sql
egistration_log.ldf'
Here the syntax I am using to do this:
alter database registration modify file (name='registration_log',filename= 'e:sql
egistration_log.ldf')
But I get the following error:
Server: Msg 5037, Level 16, State 1, Line 2
MODIFY FILE failed. Do not specify physical name.
Thank You,
John
Alter Table ......
Sep 30, 2003I have a table tblABC which exists in 800 databases. I want to run a cursor to turn off the pk field ID indentity permannetly and then to "insert tblABC select * from tblXYZ".
I have tried the "set indentity_insert tblABC on" statement and failed on insert tblABC select * from tblXYZ.
Is there any alter table related t-sql could permannetly turn off the identity?
thanks
David
Alter Table
Jun 7, 2001Is there anyway to Insert a new column between two existing columns without creating a new table?
Thanks
Alter Table
Oct 17, 2001Hi,
is it possible to override the system when adjusting
columns. i.e. a system override setting.
For instance if I've got a column that is indexed and I
want to adjust it from varchar(50) to varchar(51) it lets
me. But if I then try and adjust the column to varchar
(49) I get the error below:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'LastName' is dependent on column 'lastname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN lastname failed because one or
more objects access this column.
I asked this question on another Forum and was told I had to drop the index first. Just thought I'd ask for a second opinion.
Thanks for the help.
Steve
Alter Column
Mar 15, 2006what is the syntax for multiple column modifications ?
alter table abc
alter column x1 nvarchar(10) null
-- works
alter table abc
alter column x1 nvarchar(10) null,
alter column x2 nvarchar(10) null
-- doesn't work or do i have to use the alter table each time ?
How To Alter A Column??
Nov 15, 2007Hi,
I am new to SQL Server 2005. Please help
what is the problem with this code..?
ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL DEFAULT getdate();
Go
The error is like
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.
Alter Table
Jan 30, 2004Hi to all!
I have a silly problem, but I'm not able to solve it!
I know how to add a field in a table via Enterprise manager and I know also via ALTER TABLE command.
...But the problem is: I want to add a field NOT in the last position, but on the middle (for istance)
So, this is possible via Enterprise Manager but with the ALTER TABLE command? How can I add a field without put it in the last position?
...Thanks a lot!
Sergio
p.s.: Sorry for my poor english..I hope you understand my question!
Alter Columns?
Apr 1, 2004Hello,
I am trying to edit several tables that were imported in tab-delimited format from text files. I am trying to generate a script that will alter the data type for several different columns.
I have succesfully edited a single column with the following code:
USE THCIC
ALTER TABLE PudfTest
ALTER COLUMN
DISCHARGE VARCHAR(6) NULL
However, I have need to create a script that will change the data type for over 100 columns. So far, everything I've read tells me that multiple 'alter column' statements cannot be run in a single query. I'm hoping someone can shed some light on this, or at least point me in another direction so that I won't have to manually change the data type for every column in each of the tables.
Any help would be greatly appreciated.
Thanks!