Консультант
Профиль
Группа: Комодератор
Сообщений: 1654
Регистрация: 17.7.2003
Где: Питер
Репутация: 4
Всего: 36
|
Брррррррр...... Совсем не понял про что ты???  Спрашиваешь одно, а запрос совершенно другой | Цитата |
Можно ли переименовать название колонки с помощью Sql запроса?
|
Можно. В MsSQL для изменения названия колонки существует хранимая процедурка sp_renameЕё исходник: | Код |
CREATE PROCEDURE sp_rename
@objname nvarchar(776), -- up to 3-part "old" name
@newname sysname, -- one-part new name
@objtype varchar(13) = null -- identifying the name
as
/********1*********2*********3*********4*********5**
DOCUMENTATION:
[1] To rename a table, the @objname (meaning OldName) parm can be
passed in totally unqualified or fully qualified.
[2] The SA or DBO can rename objects owned by lesser users,
without the need for SetUser.
[3] The Owner portion of a qualified name can usually be
passed in in the omitted form (as in MyDb..MyTab or MyTab). The
typical exception is when the SA/DBO is trying to rename a table
where the @objname is present twice in sysobjects as a table
owned only by two different lesser users; requiring an explicit
owner qualifier in @objname.
[4] An unspecified Owner qualifier will default to the
current user if doing so will either resolve what would
otherwise be an ambiguity within @objtype, or will result
in exactly one match.
[5] If Database is part of the qualified @objname,
then it must match the current database. The @newname parm can
never be qualified.
[6] Here are the valid @objtype values. They correspond to
system tables which track each type:
'column' 'database' 'index' 'object' 'userdatatype'
The @objtype parm is sometimes required. It is always required
for databases. It is required whenever ambiguities would
otherwise exist. Explicit use of @objtype is always encouraged.
[7] Parms can use quoted_identifiers. For example:
Execute sp_rename 'amy."his table"','"her table"','object'
*********1*********2*********3*********4*********5*/
Set nocount on
Set ansi_padding on
Declare @objtypeIN varchar(13),
@ExecRC integer,
@CurrentDb sysname,
@CountNumNodes integer,
@UnqualOldName sysname,
@QualName1 sysname,
@QualName2 sysname,
@QualName3 sysname,
@OwnAndObjName nvarchar(517), -- "[owner].[object]"
@objid integer,
@xtype nchar(2),
@indid smallint,
@colid smallint,
@cnstid integer,
@parent_obj integer,
@xusertype smallint,
@ownerid smallint,
@objid_tmp integer,
@xtype_tmp nchar(2),
@retcode int,
@replinfo int,
@replbits int
-- initial (non-null) settings
Select @CurrentDb = db_name(),
@objtypeIN = @objtype,
@replbits = 129 --Indicates table is used in replication
-- make type case insensitive
select @objtype = lower(@objtypeIN)
------------------------------------------------------------------------
------------------- PHASE 10: Simple parm edits ---------------------
------------------------------------------------------------------------
-- Valid rename-type param?
IF (@objtype is not null AND
@objtype not in ('column', 'database', 'index', 'object', 'userdatatype'))
begin
raiserror(15249,-1,-1,@objtypeIN,0)
return 1
end
-- null names?
IF (@newname IS null)
begin
raiserror(15223,-1,11,'NewName')
return 1
end
if (@objname IS null)
begin
raiserror(15223,-1,-1,'OldName')
return 1
end
--------------- Is NewName minimally valid?
--Check for valid rename name
exec @retcode = sp_validname @newname
if @retcode <> 0
begin
raiserror(15224,-1,15,@newname)
return 1
end
-------- Parse apart the perhaps dots-qualified old name.
select @UnqualOldName = parsename(@objname, 1),
@QualName1 = parsename(@objname, 2),
@QualName2 = parsename(@objname, 3),
@QualName3 = parsename(@objname, 4)
IF (@UnqualOldName IS Null)
begin
raiserror(15253,-1,-1,@objname)
return 1
end
-- count name parts --
select @CountNumNodes = CASE WHEN @QualName3 IS NOT NULL THEN 4
WHEN @QualName2 IS NOT NULL THEN 3
WHEN @QualName1 IS NOT NULL THEN 2
ELSE 1 END
IF (@objtype = 'database' AND @CountNumNodes > 1)
begin
Raiserror(15395,-1,20,@objtypeIN)
return 1
end
if (@objtype in ('object','userdatatype') AND @CountNumNodes > 3)
begin
raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
return 1
end
---------------------------------------------------------------------------
---------------------- PHASE 20: Settle Parm1ItemType ------------------
---------------------------------------------------------------------------
------------- database?
IF (@objtype = 'database')
begin
execute @ExecRC = sp_renamedb @UnqualOldName ,@newname -- de-docu old sproc
IF @ExecRC <> 0
return 1
GOTO LABEL_51_AFTERUPDATES
end
-- assuming column/index-name, obtain object/column id's
if @QualName2 is not null
select @objid = object_id(QuoteName(@QualName2) +'.'+ QuoteName(@QualName1))
else
select @objid = object_id(QuoteName(@QualName1))
select @xtype = xtype, @replinfo = replinfo from sysobjects where id = @objid
------------ column?
if (@objtype = 'column' or @objtypeIN is null)
begin
-- find column
select @colid = NULL
if (@xtype in ('U','V'))
select @colid = colid from syscolumns
where id = @objid and name = @UnqualOldName
-- check for wrong param
if ((@colid is not null AND @objtype <> 'column') OR
(@colid is null AND @objtype = 'column'))
begin
raiserror(15248,-1,-1,@objtypeIN)
return 1
end
-- remember if we've found a column
IF (@colid is not null)
begin
if (@replinfo & @replbits <> 0)
begin
raiserror(15051,-1,-1)
return (0)
end
select @objtype = 'column'
end
end
------------ index?
if (lower(@objtype) = 'index' or @objtypeIN is null)
begin
-- find index
if (@xtype in ('U'))
select @indid = indid from sysindexes
where id = @objid and name = @UnqualOldName
AND indid NOT IN (0, 255)
-- check for wrong param
if ((@indid is not null AND @objtype <> 'index') OR
(@indid is null AND @objtype = 'index'))
begin
raiserror(15248,-1,-1,@objtypeIN)
return 1
end
if (@indid is not null)
begin
select @objtype = 'index'
select @cnstid = id, @xtype = xtype from sysobjects
where name = @UnqualOldName AND parent_obj = @objid and xtype in ('PK','UQ')
end
end
------------ object?
if (@objtype = 'object' or @objtypeIN is null)
begin
-- get object id, type
select @objid_tmp = object_id(@objname)
select @xtype_tmp = xtype, @replinfo = replinfo
from sysobjects where id = @objid_tmp
-- if object is a system table, a Scalar function, or a table valued function, skip it.
-- Cannot rename system table
if @xtype_tmp = 'S'
select @objid_tmp = NULL
-- check for wrong param
if ((@objid_tmp is not null AND @objtype <> 'object') OR
(@objid_tmp is null AND @objtype = 'object'))
begin
raiserror(15248,-1,-1,@objtypeIN)
return 1
end
if (@objid_tmp is not null)
begin
if (@xtype_tmp in ('U'))
begin
if (@replinfo & @replbits <> 0)
begin
raiserror(15051,-1,-1)
return (0)
end
end
select @objtype = 'object', @objid = @objid_tmp, @xtype = @xtype_tmp
if (@xtype in ('PK','UQ'))
select @parent_obj = parent_obj from sysobjects where id = @objid
end
end
------------ type?
if (@objtype = 'userdatatype' or @objtypeIN is null)
begin
select @xusertype = xusertype from systypes
where name = @UnqualOldName and xusertype > 256
AND (@QualName1 is null or uid = user_id(@QualName1))
-- check for wrong param
if ((@xusertype is not null AND @objtype <> 'userdatatype') OR
(@xusertype is null AND @objtype = 'userdatatype'))
begin
raiserror(15248,-1,-1,@objtypeIN)
return 1
end
if (@xusertype IS NOT null)
select @objtype = 'userdatatype'
end
---------------------------------------------------------------------
------------------- PHASE 30: More parm edits --------------------
---------------------------------------------------------------------
-- item type determined?
if (@objtype IS null)
begin
raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
return 1
end
-- was the original name valid given this type?
if (@objtype in ('object','userdatatype') AND @CountNumNodes > 3)
begin
raiserror(15225,-1,-1,@objname, @CurrentDb, @objtypeIN)
return 1
end
-- verify db qualifier is current db
if (@objtype in ('object','userdatatype'))
select @QualName3 = @QualName2
if (isnull(@QualName3, @CurrentDb) <> @CurrentDb)
begin
raiserror(15333,-1,-1,@QualName3)
return 1
end
-- get owner id and check permissions
if (@objtype = 'userdatatype')
select @ownerid = uid from systypes where xusertype = @xusertype
else
select @ownerid = ObjectProperty(@objid, 'ownerid')
if ( (not (1 = is_member('db_owner')))
AND (not (1 = is_member('db_ddladmin')))
AND (not (1 = is_member(user_name(@ownerid)))) )
begin
raiserror(15247,-1,-1)
return 1
end
-- check if system object
if (ObjectProperty(@objid, 'IsMSShipped') = 1 OR
ObjectProperty(@objid, 'IsSystemTable') = 1)
begin
raiserror(15001,-1,-1, @objname)
return 1
end
-- make sure orig no longer shows null
if @objtypeIN is null
select @objtypeIN = @objtype
-- Check for name clashing with existing name(s)
if (@newname <> @UnqualOldName)
begin
-- column name clash?
if (@objtype = 'column')
if (ColumnProperty(@objid, @newname, 'isidentity') is not null)
select @UnqualOldName = NULL
-- object name clash?
if ( (@objtype = 'object' AND @xtype in ('PK','UQ'))
OR @objtype = 'index')
if exists (select * from sysindexes where id = @objid and name = @newname
and indid not in (0,255))
select @UnqualOldName = NULL
-- index name clash?
if (@objtype = 'object' OR @cnstid IS NOT null)
if (object_id(QuoteName(user_name(@ownerid)) +'.'+ QuoteName(@newname)) is not null)
select @UnqualOldName = NULL
-- type name clash?
if (@objtype = 'userdatatype')
if exists (select * from systypes where name = @newname)
select @UnqualOldName = NULL
-- stop on clash
if (@UnqualOldName is null)
begin
raiserror(15335,-1,-1,@newname,@objtypeIN)
return 1
end
end
--------------------------------------------------------------------------
-------------------- PHASE 32: Temporay Table Isssue -------------------
--------------------------------------------------------------------------
-- Disallow renaming object to or from a temp name (starts with #)
if (@objtype = 'object' AND
(substring(@newname,1,1) = N'#' OR
substring(object_name(@objid),1,1) = N'#'))
begin
raiserror(15600,-1,-1, 'sp_rename')
return 1
end
--------------------------------------------------------------------------
-------------------- PHASE 34: Cautionary messages --------------------
--------------------------------------------------------------------------
if @objtype = 'column'
begin
-- Check for Dependencies: No column rename if enforced dependency on column
IF EXISTS (SELECT * FROM sysdepends WHERE depid = @objid AND depnumber = @colid AND deptype > 0)
begin
raiserror(15336,-1,-1, @objname)
return 1
end
end
else if @objtype = 'object'
begin
-- Check for Dependencies: No RENAME or CHANGEOWNER of OBJECT when exists:
IF EXISTS (SELECT * FROM sysdepends d WHERE
d.depid = @objid -- A dependency on this object
AND d.deptype > 0 -- that is enforced
AND @objid <> d.id -- that isn't a self-reference (self-references don't use object name)
AND @objid <> -- And isn't a reference from a child object (also don't use object name)
(SELECT o.parent_obj FROM sysobjects o WHERE o.id = d.id)
)
begin
raiserror(15336,-1,-1, @objname)
return 1
end
end
-- WITH DEFERRED RESOLUTION, SYSDEPENDS IS NOT VERY ACCURATE, SO WE ALSO
-- RAISE THIS WARNING **UNCONDITIONALLY**, EVEN FOR NON-OBJECT RENAMES
raiserror(15477,-1,-1)
-- warn about dependencies...
if (@objtype = 'objects' and exists (select * from sysdepends where depid = @objid))
raiserror(15337,-1,-1)
--------------------------------------------------------------------------
--------------------- PHASE 40: Update system tables ------------------
--------------------------------------------------------------------------
-- obtain owner-qual object name (for most below)
select @OwnAndObjName = QuoteName(user_name(@ownerid))+'.'+QuoteName(object_name(@objid))
-- DO THE UPDATES --
if (@objtype = 'userdatatype') -------- change type name
UPDATE systypes set name = @newname where xusertype = @xusertype
else if (@objtype = 'object') -------- change object name
begin
BEGIN TRANSACTION
-- Locks Object and increments schema_ver
DBCC LockObjectSchema(@OwnAndObjName)
-- update the object name
UPDATE sysobjects set name = @newname where id = @objid
-- update index-cnst name (no rows changed if not 'PK' or 'UQ')
if (@xtype in ('PK','UQ'))
UPDATE sysindexes set name = @newname where id = @parent_obj and name = @UnqualOldName
-- update base/text index name (no rows changed if not there)
else if (@xtype = 'U')
begin
UPDATE sysindexes set name = @newname where id = @objid AND indid = 0
UPDATE sysindexes set name = convert(sysname,'t'+@newname)
where id = @objid AND indid = 255
end
COMMIT TRANSACTION
end
else if (@objtype = 'index') -------- change index name
begin
BEGIN TRANSACTION
-- Locks Object and increments schema_ver.
DBCC LockObjectSchema(@OwnAndObjName)
-- update the index name
UPDATE sysindexes set name = @newname where id = @objid and indid = @indid
-- change object name if cnst
if (@cnstid IS NOT null)
UPDATE sysobjects set name = @newname where id = @cnstid
COMMIT TRANSACTION
end
else if (@objtype = 'column') -------- change column name
begin
-- Use DBCC to check for column in use by check-constraint, computed-column, etc
-- THIS IS NOT A DOCUMENTED DBCC: DO NOT USE DIRECTLY!
DBCC RENAMECOLUMN ( @OwnAndObjName, @UnqualOldName, @newname )
end
------------------------- Finalization -----------------------
LABEL_51_AFTERUPDATES:
Raiserror(15338,-1,-1,@objtypeIN,@newname)
return 0 -- sp_rename
GO
|
Процедура умная и умеет многое (подробнее смотри SQL Server Books Online) Если внимательно просмотреть исходник, то можно вытащить строку: | Код |
DBCC RENAMECOLUMN (discounts, column_a, new)
|
она-то нам и нужна(ну это если не охота пользаваться sp) функция эта не документированная, но работает(только что проверил). Можно вообще пойти путём извращений - создать новый столбец с новым названием, заполнить его значениями и удалить старый. IMHO - гемор!!!! --------------------
подписи нет |
Переименование колонок 
Загрузка ...
