azana-afyza.com

If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

You can use this procedure to checks the path where SQL Server is installed. It also lists out all the data (.mdf) and log (.ldf ) files. Also list the quantity of tables, views, sp, xp and users on the given database.

This procedure is developed by:

/*
**Author Rodrigo Acosta
**E-mail racosta@iecisa.com.ar
*/

CREATE PROCEDURE proc_list_ing
@dbname varchar(20)= NULL
AS
SET NOCOUNT ON
IF @dbname IS NULL
BEGIN
PRINT ‘*****************************************’
PRINT ‘**You must enter a Database Name **’
PRINT ‘**to continue. **’
PRINT ‘*****************************************’
PRINT ”
PRINT ‘Available Databases…’
EXEC master..sp_databases
RETURN
END

IF NOT EXISTS (
SELECT name
FROM master..sysdatabases
WHERE name=@dbname
)
BEGIN
PRINT ‘*****************************************’
PRINT ‘**The database does not exists. **’
PRINT ‘**Available databases… **’
PRINT ‘*****************************************’
PRINT ”
EXEC master..sp_databases
RETURN
END

/*
**Gets the path where the db files are located
**and then puts the list in temp tables.
*/
DECLARE @lenght_db varchar(300)
DECLARE @path_db varchar(300)
DECLARE @numero_db int
SET @lenght_db=(
SELECT filename
FROM master..sysfiles
WHERE name=’master’
)
SET @path_db=(
SELECT filename
FROM master..sysfiles
WHERE name=’master’
)
set @lenght_db=LEN (@lenght_db)
set @numero_db=@lenght_db-10
SET @path_db= left(@path_db,@numero_db)

DECLARE @cmd_mdf varchar(300)
DECLARE @cmd_ldf varchar(300)

SET @cmd_mdf=’EXEC master..xp_cmdshell “dir /B ‘+@path_db+’*.mdf”‘
SET @cmd_ldf=’EXEC master..xp_cmdshell “dir /B ‘+@path_db+’*.ldf”‘

CREATE TABLE #datafiles_mdf
(mdf varchar(40))

CREATE TABLE #datafiles_ldf
(ldf varchar(40))

/*MDF’s y LDF’s saved on tables*/
INSERT INTO #datafiles_mdf EXEC (@cmd_mdf)
INSERT INTO #datafiles_ldf EXEC (@cmd_ldf)

———————————————–
/*
**Checks the user tables on the db
*/
DECLARE @select_table varchar(100)
SET @select_table=’SELECT count(name)AS “Users Tables” FROM ‘+@dbname+’..sysobjects where xtype=”u”‘

———————————————–
/*
**Checks the views
*/
DECLARE @select_view varchar(100)
SET @select_view=’SELECT count(name) AS “Views” FROM ‘+@dbname+’..sysobjects where xtype=”v” ‘

———————————————–
/*
**Checks the system tables
*/
DECLARE @select_sistema varchar(100)
SET @select_sistema=’SELECT count(name)AS “system Tables” FROM ‘+@dbname+’..sysobjects where xtype=”s” ‘

———————————————–
/*
**Checks the SP
*/
DECLARE @select_sp varchar(100)
SET @select_sp=’SELECT count(name) AS “Stored Procedures” FROM ‘+@dbname+’..sysobjects where xtype=”p”‘

———————————————–
/*
**Checks the XP
*/
DECLARE @select_xp varchar(100)
SET @select_xp=’SELECT count(name) AS “Extended Procedures” FROM ‘+@dbname+’..sysobjects where xtype=”x”‘

———————————————–
/*
**Checks the user names
*/
DECLARE @select_users varchar(100)
SET @select_users=’SELECT name AS “Users names” FROM ‘+@dbname+’..sysusers where name not like “db_%”‘

—————————————————————–
/*
**Print the result
*/
PRINT ‘List of .mdf files on the path ‘+@path_db
select mdf as ‘Data files’ from #datafiles_mdf
PRINT ‘List of .ldf files on the path ‘+@path_db
select ldf AS ‘Log files’ from #datafiles_ldf
print ‘Quantity of user tables on the database ‘+@dbname
EXEC (@select_table)
print ‘Quantity of system tables on the database ‘+@dbname
EXEC (@select_sistema)
print ‘Quantity of views on the database ‘+@dbname
EXEC (@select_view)
print ‘Quantity of Stored Procedures on the database ‘+@dbname
EXEC (@select_sp)
print ‘Quantity of Extended Procedures on the database ‘+@dbname
EXEC (@select_xp)
PRINT ‘Users on the database ‘+@dbname
EXEC (@select_users)