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
-
|
