SQL存储过程在SQL数据库中用途广泛,下面为您介绍如何定义SQL存储过程,如果您是刚接触SQL数据库的用户,不妨一看,希望对您学习SQL存储过程有所帮助。
- CREATE PROCEDURE get_tableinfo AS
- if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
- create table tablespaceinfo --创建结果存储表
- (nameinfo varchar(50) ,
- rowsinfo int , reserved varchar(20) ,
- datainfo varchar(20) ,
- index_size varchar(20) ,
- unused varchar(20) )
- delete from tablespaceinfo --清空数据表
- declare @tablename varchar(255) --表名称
- declare @cmdsql varchar(500)
- DECLARE Info_cursor CURSOR FOR
- select o.name
- from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1
- and o.name not like N'#%%' order by o.name
- OPEN Info_cursor
- FETCH NEXT FROM Info_cursor
- INTO @tablename
- WHILE @@FETCH_STATUS = 0
- BEGIN
- if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
- execute sp_executesql
- N'insert into tablespaceinfo exec sp_spaceused @tbname',
- N'@tbname varchar(255)',
- @tbname = @tablename
- FETCH NEXT FROM Info_cursor
- INTO @tablename
- END
- CLOSE Info_cursor
- DEALLOCATE Info_cursor
- GO
【编辑推荐】