-- if sp exists, drop it
IF EXISTS ( 	SELECT * 
		FROM sysobjects 
		WHERE id = object_id(N'[dbo].[usp_DBinfo]')
		and OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE [dbo].[usp_dbinfo] 
GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON 
GO

-- create procedure usp_dbinfo with 2 input parameters
CREATE PROCEDURE usp_dbinfo @DBname sysname = null, @VType varchar(25) 
AS
DECLARE 
@TName varchar(50), 		-- Table name
@FName varchar(50),		-- Field name
@Len varchar(30),		-- Field length
@DType varchar(15),		-- Data type
@Cmd varchar(30),		-- Command 
@Id varchar (20),		-- Table object id
@Nulls varchar (20)		-- Allows nulls

SET NOCOUNT ON

-- see if database exists
IF not exists ( SELECT name
		FROM master.dbo.sysdatabases 
		WHERE (name = @DBname))
BEGIN

-- if not, raise error
RAISERROR(15010,-1,-1, @DBname)
RETURN (1)
END

-- print exec msg 
PRINT 'Executing: usp_dbinfo for ' + UPPER(@DBname)+ ' ... '
PRINT ''

-- to view server jobs
IF @VType = 'jobs'
BEGIN
SELECT @Cmd = 'USE msdb exec sp_help_job'
EXEC (@Cmd)
RETURN (1) 
END

-- to view server alerts
IF @VType = 'alerts'
BEGIN
SELECT @Cmd = 'USE msdb exec sp_help_alert'
EXEC (@Cmd)
RETURN (1)
END

-- to view database properties & space allocation info
IF @VType = 'database'
BEGIN
DBCC CHECKALLOC WITH ALL_ERRORMSGS 
EXEC sp_helpdb @DBname
RETURN (1)
END

-- to view database objects 
IF @VType = 'objects' 
BEGIN
EXEC sp_help
RETURN (1)
END

-- to view database users 
IF @VType = 'users'
BEGIN
EXEC sp_helpuser
RETURN (1)
END

-- to view database tables & info
IF @VType = 'tables'
BEGIN
PRINT '** ALL TABLE INFORMATION **'
END

-- declare main cursor to get first user table name from sysobjects
DECLARE TabNameCur CURSOR FOR 

	SELECT  so.name 
	FROM sysobjects so 
	WHERE so.xtype = 'u'
	ORDER BY name

-- open cursor and fetch table info data
OPEN  TabNameCur FETCH TabNameCur INTO @TName

-- while fetch status is 0, fetch  table info
WHILE @@FETCH_STATUS = 0
BEGIN 

-- display space used for table
EXEC sp_spaceused @TName

PRINT '------------------- ' + @TName + ' -------------------------------'

-- declare cursor to get table info
DECLARE TableInfoCur CURSOR FOR 

	-- get all table information (linked to main cursor by @TabName)
	SELECT DISTINCT sc.name, sc.length, st.name, sc.id, st.allownulls
	FROM sysobjects so 
	INNER JOIN syscolumns sc ON so.id = sc.id 
	INNER JOIN systypes st ON sc.xtype = st.xtype

	WHERE (so.xtype = 'U')   
	and st.name <> 'sysname'
	and so.name = @TName

-- open tableinfo nested cursor to get table info
OPEN  TableInfoCur

-- print item headers
PRINT ''
PRINT '' + CAST('NAME' as char(30)) + '' + CAST('OBJ ID' as char(7)) + ' ' + 
CAST('NULLS?' as char(6)) + '  ' + CAST('TYPE' as char(10)) + ' ' + CAST('LEN' as char(3))
PRINT ''

-- fetch items into nested cursor
FETCH TableInfoCur INTO @FName, @Len , @DType, @Id, @Nulls

-- if fetch status less than 0, there are no user tables
IF @@FETCH_STATUS < 0
PRINT 'No User Tables'

-- while fetch status = 0, print table info
WHILE @@FETCH_STATUS = 0
BEGIN

-- print data
PRINT '' + CAST (@FName as char(30)) + CAST (@Id as char(6)) + '  ' + CAST (@Nulls as char(8))
+ '' + CAST (@DType as char(10)) + ' (' + rtrim(@Len)+ ') '

-- fetch next row of table data
FETCH NEXT FROM TableInfoCur 
INTO @FName, @Len , @DType, @Id, @Nulls 
END

-- close & deallocate nested cursor
CLOSE TableInfoCur DEALLOCATE TableInfoCur

-- get next database name from main cursor
FETCH NEXT FROM TabNameCur INTO @TName
END

-- close & deallocate main cursor
CLOSE TabNameCur DEALLOCATE TabNameCur

-- Author: Lauryn Bradley  02/02/02
-- Shows various info for active database
-- Accepts viewtype parameter of either: 
-- 	'database', 'tables', 'users', 'objects', 'alerts' or 'jobs'

-- EXEC usp_dbinfo 'database_name', 'viewtype_name'
GO