Geeks With Blogs
Rob Foster's House of Southern-Fried SharePoint and other ramblings on enterprise technologies

I've been playing around with SQL 2008's Intellisense features and ran across a few of the "sys." ('sys dot') functions that are very useful. 

Naturally, I cracked open one of the ambiguous SharePoint content database and started running some analysis queries about the database.  I came up with a very high-level and then a more detailed level SQL script that you can use for some very quick database analysis.  No this is not as robust as a tool that you can buy that does deep analysis, but it does give you a good high-level view of the database that you are analyzing. 

Enjoy!

//begin script

 --high-level database report
SELECT COUNT(*) AS 'Number of Tables'
FROM sys.tables

SELECT COUNT(*) AS 'Number of Triggers'
FROM sys.triggers

SELECT COUNT(*) AS 'Number of Views'
FROM sys.views

SELECT COUNT(*) AS 'Number of Sprocs'
FROM sys.procedures

SELECT COUNT(*) AS 'Number of Indexes'
FROM sys.indexes

SELECT COUNT(*) AS 'Number of Database files'
FROM sys.database_files

SELECT COUNT(*) AS 'Number of SQL Logins'
FROM sys.sql_logins

SELECT COUNT(*) AS 'Number of Assemblies'
FROM sys.assemblies


--Detailed Database Report
SELECT name as 'Table Name'
  ,create_date
  ,modify_date
FROM sys.tables
ORDER BY name

SELECT t.name AS 'Trigger Name', tbl.name as 'Associated Table'
FROM sys.triggers t
INNER JOIN sys.tables tbl
 ON t.parent_id = tbl.object_id
ORDER BY t.name

SELECT name AS 'View Name'
FROM sys.views
ORDER BY name

SELECT name AS 'SProc Name', create_date, modify_date
FROM sys.procedures
ORDER BY name

SELECT tbl.name as 'Associated Table'
  , i.name as 'Index Name'
  , i.type_desc
  , i.is_unique
  , i.is_primary_key
  , i.is_unique_constraint
FROM sys.indexes i
INNER JOIN sys.tables tbl
 ON i.object_id = tbl.object_id
ORDER BY tbl.name, i.name

SELECT name AS 'Database File Name'
  ,type_desc
  ,physical_name
  ,state_desc
  ,size
  ,growth
  ,is_read_only
  ,is_sparse
  ,is_percent_growth
FROM sys.database_files
ORDER BY name

SELECT name AS 'SQL Login Name'
,[sid]
,type_desc
,default_database_name
,default_language_name
FROM sys.sql_logins
ORDER BY name

SELECT name AS 'Assembly Name'
  ,clr_name,permission_set_desc
  ,create_date 
  ,modify_date
FROM sys.assemblies
ORDER BY name

//end script

Posted on Tuesday, September 9, 2008 9:40 AM Architecture , SharePoint , Office Productivity | Back to top


Comments on this post: Quick SQL 2008 DB Analysis Script

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Rob Foster | Powered by: GeeksWithBlogs.net