初学 -- sql几点基础知识

1）查询某表主键
select column_name  from information_schema.key_column_usage where table_name = '表名' and constraint_name like 'PK_%'

2）sql命令，像文本文件中写内容
declare @cmd sysname,@var sysname
set @var='文件的内容'
set @cmd='echo ' +@var+' >E://文件名.txt'
exec master..xp_cmdshell @cmd

3）sql事务
SET XACT_ABORT ON  -- 执行失败自动回滚，用于存储过程
save tran point --保存回滚点
rollback tran point  -- 回滚至回滚点

4）游标实例
declare @name varchar(20)
declare mycursor cursor
for
  select categoryname from categories
  open mycursor
  fetch mycursor into @name
  while @@fetch_status<>-1
    if @@fetch_status <>-2
      begin
       print @name
       fetch mycursor into @name
      end
  close mycursor
  deallocate mycursor

5）sp_executesql 执行sql语句
create proc a
 @tableName varchar(20),
 @RowCount int output
as
 declare @rowcountstring nvarchar(500)
  set @rowcountstring='select @RowCount = count(*) from '+@tableName
  exec sp_executesql @rowcountstring,N'@RowCount int output',@RowCount output
go
declare @count int
exec a 'products',@count output
print @count