sql server ÖÐɾ³ýĬÈÏÔ¼ÊøµÄͨÓÃsql½Å±¾
×÷Õߣº sealyu ÈÕÆÚ£º2008-04-17
ÔÚSQL Server ÖУ¬Èç¹û¸ø±íµÄÒ»¸ö×Ö¶ÎÉèÖÃÁËĬÈÏÖµ£¬¾Í»áÔÚϵͳ±ísysobjectsÖÐÉú³ÉÒ»¸öĬÈÏÔ¼Êø¡£
Èç¹ûÏëɾ³ýÕâ¸öÉèÖÃÁËĬÈÏÖµµÄ×ֶΣ¨¼ÙÉè´Ë×Ö¶ÎÃûcolumn1£©£¬
Ö´ÐГALTER TABLE table1 DROP COLUMN column1”ʱ¾Í»á±¨´í£º
The object 'DF__xxxxxxxxxxx' is dependent on column 'column1'.
ALTER TABLE DROP COLUMN column1failed because one or more objects access this column.
ËùÒÔÔÚɾ³ý´Ë×Ö¶ÎʱÐèÒªÏȽ«ÏµÍ³±íÖеĶÔӦĬÈÏÔ¼Êøɾ³ý£¬ ¿ÉÒÔʹÓÃÏÂÃæµÄ½Å±¾½øÐÐɾ³ý£º
-- this script drops the default constraint which is generated by the setting of default value.
DECLARE @tablename VARCHAR(100), @columnname VARCHAR(100), @tab VARCHAR(100)
SET @tablename='CountryGroupEmailAndWaitAux'
SET @columnname='actionOfHasNoValidEmail'
declare @defname varchar(100)
declare @cmd varchar(100)
select @defname = name
from sysobjects so
JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = @tablename
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid from syscolumns
WHERE id = object_id(@tablename) AND
name = @columnname)
select @cmd='alter table '+ @tablename+ ' drop constraint '+ @defname
if @cmd is null print 'No default constraint to drop'
exec (@cmd)
ÔÚɾ³ý¶ÔÓ¦µÄĬÈÏÔ¼Êøºó£¬Ö´ÐУº
ALTER TABLE table1 DROP COLUMN column1
¼´¿Éɾ³ý×ֶΡ£
Ïà¹ØÎĵµ£º
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾Ý¿â
CREATE DATABASE data ......
˵¾ä´ó·Ï»°£¬ÄǾÍÊÇ“ºÏÀíµÄƽºâ¸÷ÖÖ×ÊÔ´µÄʹÓÃ,ÄÚ´æ,cpu,io µÈµÈ”¡£ÕâÒ²ÊǺÜÓеÀÀíµÄ£¬µ«ÊÇʵ¼Ê¾Í²»ºÃ×öÁË£¬ÈçºÎƽºâÄØ£¿
¾ßÌåµãµÄô£¬Í¨¹ý±È½Ï£¬Ó¦¸ÃÊÇÏìӦʱ¼ä×öΪÖ÷ÒªµÄÆÀÅÐÒòËØÁË¡£µ«ÊÇ¿ÉÄÜÓÉÓÚһЩ²»È·¶¨µÄÒòËØ£¬¿ÉÄÜ»á³öÏÖ£ºÕâ´ÎÅܵĿ죬²»µÈÓÚÏ´ÎÒ²ÅܵĿìÁË¡£
ÄǾÍÒª¸ù¾Ý±íµÄÊý¾ÝÁ¿µÄ±ä»¯£¬È·¶¨Ò»¸ö± ......
ÏÖÒªÇó²éѯ½çÃ棺²»ÂÛ15λ»òÕß18λÉí·ÝÖ¤ºÅ¶¼Äܲéѯ³öÊý¾Ý¿âÖÐËùÓе±Ç°Óû§ÐÅÏ¢¡£
·½°¸1£º
create or replace function CONVERT_ID_15 (/*ת»»Éí·ÝÖ¤ºÅΪ15λ*/
p_id2 in varchar2
) return varchar2
is
p_id varchar2(20);
id varchar2(15);
begin
p_id:=ltrim(p_id2);
p_id ......
±³¾°£ºDB2µÄÊý¾Ý¿âÐÔÄܺÜÅ£X£¬µ«ÊÇÆäÎĵµÈ´ºÜ²î£¬ÓÈÆäÊÇ¿ª·¢²Î¿¼Îĵµ£¬¶¼ÊÇÓ¢Îĵģ¬ä¯ÀÀµÄʱºò»¹ºÜ²»ºÃÕÒ£¬ÐèÒªÉÏIBMµÄÍøÕ¾¿´£¬ÍøÕ¾Ò²³öÆæµÄÂý£¬¼«²»·½±ã£¬Èÿª·¢ÈËÔ±¾Ù²½Î¬¼è£¬ÕâÒ²ÐíÊÇIBM DB2µÄÓû§ÉÙ£¬ÊéÉÙ£¬×ÊÁÏÉÙµÄÔÒò¡£
££££££££££££££££££
´´½¨SQL´æ´¢¹ý³Ì£¨CREATE PROCEDURE (SQL) stat ......
Select * from tableName
exec('select * from tableName')
exec sp_executesql N'select * from tableName' -- Çë×¢Òâ×Ö·û´®Ç°Ò»¶¨Òª¼ÓN
2:×Ö¶ÎÃû£¬±íÃû£¬Êý¾Ý¿âÃûÖ®Àà×÷Ϊ±äÁ¿Ê±£¬±ØÐëÓö¯Ì¬SQL
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- ´íÎó,²»»áÌáʾ´í ......