create proc getmaxvalue @table varchar(20),@value output as select @value=Max(id) from @table
create proc getmaxvalue @table varchar(20),@value integer output as select @value=Max(id) from @table
create proc getmaxvalue @table varchar(20), @value int output as declare @SQL nvarchar(2000) set @SQL = 'select @Value = max(id) from ' + 'ccsclientcode' exec sp_executesql @SQL, N'@Value int output', @Value = @Value out
create proc getmaxvalue @table varchar(20), @value int output as declare @SQL nvarchar(2000) set @SQL = 'select @Value = max(id) from ' + @table exec sp_executesql @SQL, N'@Value int output', @Value = @Value out
E. 使用 OUTPUT 游标参数 OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。首先,创建以下过程,在 titles 表上声明并打开一个游标:USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_cursor' and type = 'P') DROP PROCEDURE titles_cursor GO CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT AS SET @titles_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM titlesOPEN @titles_cursor GO接下来,执行一个批处理,声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。USE pubs GO DECLARE @MyCursor CURSOR EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT WHILE (@@FETCH_STATUS = 0) BEGIN FETCH NEXT FROM @MyCursor END CLOSE @MyCursor DEALLOCATE @MyCursor
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='表名'
3: 以下这个查询表结构的语句可能大家都见过:
利用syscolumns,sysindexes,sysindexkeys三个表关联可以查出主键
SELECT (case when a.colorder=1 then d.name else '' end) N'表名', a.colorder N'字段序号', a.name N'字段名', (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识', (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '√' else '' end) N'主键', b.name N'类型', a.length N'占用字节数', COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', (case when a.isnullable=1 then '√'else '' end) N'允许空', isnull(e.text,'') N'默认值', isnull(g.[value],'') AS N'字段说明' --into ##tx
FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder
as
select @value=Max(id) from @table
as
select @value=Max(id) from @table
as
declare @SQL nvarchar(2000)
set @SQL = 'select @Value = max(id) from ' + 'ccsclientcode'
exec sp_executesql @SQL,
N'@Value int output',
@Value = @Value out
as
declare @SQL nvarchar(2000)
set @SQL = 'select @Value = max(id) from ' + @table
exec sp_executesql @SQL,
N'@Value int output',
@Value = @Value out
OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。首先,创建以下过程,在 titles 表上声明并打开一个游标:USE pubs
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'titles_cursor' and type = 'P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT *
FROM titlesOPEN @titles_cursor
GO接下来,执行一个批处理,声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。USE pubs
GO
DECLARE @MyCursor CURSOR
EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @MyCursor
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
1:利用SQL自带的存储过程
EXEC sp_pkeys @table_name='表名'
2:利用系统表INFORMATION_SCHEMA.KEY_COLUMN_USAGE
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME='表名'
3: 以下这个查询表结构的语句可能大家都见过:
利用syscolumns,sysindexes,sysindexkeys三个表关联可以查出主键
SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##tx
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder