几个简单的存储过程

由于感觉在 SQLCMD 中获取数据库、表、视图信息上不是那么方便，便自己动手写了几个存储过程。

----------------------------------------------------------------------------------------------------------

use master;
go
-- 获取实例中用户数据库的信息
if object_id('dbo.sp_database') is not null
 drop procedure dbo.sp_database;
go
create procedure dbo.sp_database
as

set nocount on;
declare @desc varchar(max); set @desc='';

select @desc=@desc+'Database Name: '+[name]+char(10)+char(13)+
'Recovery Model: '+[recovery_model_desc]+char(10)+char(13)+
'State: '+[state_desc]+' Access: '+[user_access_desc]+char(10)+char(13)+
'Collation: '+[collation_name]+
char(10)+char(13)+replicate('-',40)+char(10)+char(13)
from sys.databases where database_id>4;

print @desc;
go
exec sp_ms_marksystemobject 'dbo.sp_database';
go

 

-- 获取当前数据库中的用户表名
if object_id('dbo.sp_table') is not null
 drop procedure dbo.sp_table;
go
create procedure dbo.sp_table  
as

set nocount on;

declare @desc varchar(max); set @desc='';

select @desc=@desc+quotename(schema_name([schema_id]))+
'.'+quotename([name])+char(10)+char(13)
from sys.tables order by [schema_id];

print @desc;
go
exec sp_ms_marksystemobject 'dbo.sp_table';
go

 

-- 获取当前数据库中的视图名
if object_id('dbo.sp_view') is not null
 drop procedure dbo.sp_view;
go
create procedure dbo.sp_view  
as

set nocount on;
declare @desc varchar(max); set @desc='';

select @desc=@desc+quotename(schema_name([schema_id]))+
'.'+quotename([name])+char(10)+char(13)
from sys.views order by [schema_id];

print @desc;
go
exec sp_ms_marksystemobject 'dbo.sp_view';
go

 

-- 获取指定用户表或视图的列信息

if object_id('dbo.sp_column') is not null
 drop procedure dbo.sp_column;
go
create procedure dbo.sp_column @tabname varchar(776)
as

set nocount on;
if parsename(@tabname,3) is not null and parsename(@tabname,3)<>db_name()
begin
 print 'The object must be in the current database.';
 return ;
end

if object_id(@tabname,'U') is null and object_id(@tabname,'V') is null
begin
 print 'Invalid table or view name '''+@tabname+'''.';
 return ;
end

declare @desc varchar(max); set @desc='';

select @desc=@desc+cast([name] as char(40))+
cast((case is_nullable when 1 then 'NULL' else 'NOT NULL' end) as char(9))+
type_name([system_type_id])+
(case when [system_type_id] in (165,167,173,175,231,239) and max_length>1
 then '('+ltrim(max_length)+')'
 when [system_type_id] in (165,167,231) and max_length=-1
 then '(max)'
 when [system_type_id] in (106,108)
 then '('+ltrim([precision])+','+ltrim(scale)+')'
 else '' end)+char(10)+char(13)
from sys.all_columns
where [object_id]=object_id(@tabname)

print @desc;
go
exec sp_ms_marksystemobject 'dbo.sp_column';
go

 

-- 获取表间引用的关系

if object_id('dbo.sp_reference') is not null
 drop procedure dbo.sp_reference;
go
create procedure dbo.sp_reference
@tabname varchar(776), @colname varchar(128)
as
set nocount on;
if parsename(@tabname,3) is not null and parsename(@tabname,3)<>db_name()
begin
 print 'The object must be in the current database.';
 return ;
end

declare @obj_id int; set @obj_id=object_id(@tabname);
if @obj_id is null
begin
 print 'Invalid table name '''+@tabname+'''.';
 return ;
end

declare @col_id int;set @col_id=columnproperty(@obj_id,@colname,'columnid');
if @col_id is null
begin
 print 'Invalid column name '''+@colname+'''.';
 return ;
end

declare @desc varchar(max); set @desc='';
select @desc=@desc+'>> '+quotename(object_name(parent_object_id))+'.'+
quotename(col_name(parent_object_id,parent_column_id))+char(10)+char(13)
from sys.foreign_key_columns
where referenced_object_id=@obj_id and referenced_column_id=@col_id

select @desc=@desc+'<< '+quotename(object_name(referenced_object_id))+'.'+
quotename(col_name(referenced_object_id,referenced_column_id))+char(10)+char(13)
from sys.foreign_key_columns
where parent_object_id=@obj_id and parent_column_id=@col_id

print @desc;
go
exec sp_ms_marksystemobject 'dbo.sp_reference';
go

----------------------------------------------------------------------------------------------------------------

计划写更多的适用于 SQLCMD 的存储过程，最终目的是在 SQLCMD 中可以方便得获取各种帮助信息，包括数据库元数据，各类语句的帮助（类似于 ORACLE 中的 SQLPLUS）。