----查看表信息
declare @table_name as varchar(max)
set @table_name ='表名'
select sys.columns.name, sys.types.name,sys.columns.max_length, sys.columns.is_nullable,
(select count(*) from sys.identity_columns where sys.identity_columns.object_id
= sys.columns.object_id and sys.columns.column_id = sys.identity_columns.column_id) as is_identity ,
(select value from sys.extended_properties where sys.extended_properties.major_id = sys.columns.object_id
and sys.extended_properties.minor_id = sys.columns.column_id) as description
from sys.columns, sys.tables, sys.types where sys.columns.object_id
= sys.tables.object_id
and sys.columns.system_type_id=sys.types.system_type_id and sys.tables.name=@table_name
order
by sys.columns.column_id
----查看表信息
游标查询使用
declare @变量1
declare @变量2
SET @suminsuredamount=0
Declare curStudentFee1 Cursor for select 数据1,数据2 from 表 where 条件
Open curStudentFee1
Fetch Next From curStudentFee1 Into @变量1,@变量2
While ( @@Fetch_Status=0 )
begin
使用这些变量
Fetch Next From curStudentFee1 Into @变量1,@变量2
end
Close curStudentFee1
Deallocate curStudentFee1
循环插入多条数据
INSERT INTO [表名]
( 字段1,字段2)
( select 字段1,字段2 from 表名 where ...)
--Description: <拆分字符串函数>
--select sp from [dbo].[Split]('1,2,3',',')
CREATE FUNCTION[dbo].[Split]
(
@SplitString nvarchar(max),
-- 如果要传入NText类型,下面需要相应的修改,注释行为NText下同
@Separator nvarchar(2)
)
RETURNS @SplitStringsTable TABLE
(sp nvarchar(max)
)
AS
BEGIN
DECLARE @CurrentIndex int
DECLARE @NextIndex int
DECLARE @ReturnText nvarchar(max)
SELECT @CurrentIndex=1
WHILE (@CurrentIndex<=datalength(@SplitString)/2)
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex)
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=datalength(@SplitString)/2+1
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex)
INSERT INTO @SplitStringsTable(sp) VALUES(@ReturnText)
SELECT @CurrentIndex=@NextIndex+1
END
RETURN
END
循环拆分
declare @str varchar(50)
declare @spliter varchar(10)
declare @skhc varchar(50)
DECLARE @Num int,@Pos int, @NextPos int
set @str='1,2,3,4,5,6'
set @spliter=','
SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@str))
BEGIN
SELECT @NextPos = CHARINDEX(@spliter, @str, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@str) + 1
SET @skhC =(RTRIM(LTRIM(SUBSTRING(@str, @Pos, @NextPos - @Pos))))
SET @Pos = @NextPos+1
--print @skhc
END
更新字段
update 表1 set 表1字段=表2字段
from 表2 where
表1字段=表2字段
Case具有两种格式。简单Case函数和Case搜索函数。
--简单Case函数
CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END