[小问题笔记(八)] 常用SQL(读字段名,改字段名,打印影响行数,添加默认值,查找存储过程等)

时间:2022-11-26 02:26:08

读取所有字段,自然排序

declare  @fields varchar(max) 

Select @fields=ISNULL(@fields,'')++name+',' from syscolumns Where ID=OBJECT_ID('contact') order by colorder

print @fields

读取所有字段,名称排序

declare  @fields varchar(max)
Select @fields=ISNULL(@fields,'')++name+',' from syscolumns Where ID=OBJECT_ID('contact') order by name
print @fields;

添加字段、更改类型、改名、删字段

alter table CompeteAgentInfo add isFangTuPay int Null,isFangTu int Null

alter table contacttrail alter column  TrailDescription nvarchar(2000)

sp_rename 'Contacttrail.trailisvalid','IsValid','column'

ALTER TABLE table_name  DROP COLUMN column_name 

打印影响行数:

print @@rowcount

更改表名

EXEC sp_rename 'sshop1', 'Shop'

已有字段添加默认值

alter table AccompanyVisit add constraint AccompanyVisit_isvalid DEFAULT 1 for isvalid

查找存储过程(包含XiaoBu)

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%XiaoBu%' AND ROUTINE_TYPE='PROCEDURE'