Developing a comprehensive database metadata report
-
Good morning,
I'm working on developing a report that is somewhat more complex than a standard Data Dictionary, and I'm not sure where to start. I've spent a good amount of time playing around on the net and can't seem to find anything to get me started. I don't have access to anything beyond SSMS, which may be the problem, but we'll see if I can do this in basic SQL.
What I'd like is to have a complete list of all tables and their columns, and then the following fields:
Table_Name
Column_Name
Number_Of_Rows (this would repeat for each column in the table which is fine)
Number_of_Nulls (or Percentage Nulls. I want to see how many times each column is filled in or not)
Key (whether it's a primary or foreign key)
Min_Value (for numeric and date fields)
Max_Value (for numeric and date fields)
Data Type
Precision
Length
I have found queries that provide me all but the italicized fields. The code I'm working with right now is:
SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,i.rows, c.name
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
inner join sys.columns c on t.object_id = c.object_id
where SCHEMA_NAME(t.schema_id)='TestSalesDB'
order by t.name, c.name
Thanks so much for any suggestions.......
CB
-
well, there is a few contributions here on SSC that does some of what you are after and much, much more:
http://www.sqlservercentral.com/search/?q=database+documentation&t=s&sort=relevance
one of the four links for "Comprehensive Database Documentation" is what i've personally used.
i've modified one of those versions myself, and that version is here:
documentation enhanced sp_dbDocumentation.txt
my version includes the table/view/proc/function definitions as well in the same report, but note it has an additiona dependency to a procedure i created for exporting table/proc definitions.
it produces an html document you can save; the results look like this example:
Sample Database Documentation Results From DEV223_master.html
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Great opportunity for a third party product. Red Gate SQL Doc[/url] does this extremely well.
And, disclaimer, I work for Red Gate.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thanks Lowell, I'll have to review this a little more closely after I'm out of my meeting, but (and this may simply be a case of my missing it) but I don't see how to create the percentage nulls for each field and the min / max values. But as I said, I'll take a look in a bit.
Thanks again 🙂
-
Thanks Grant - I've actually been a "fan" of yours for a while, so well aware that you work for REDGate :-). I've been at this job for a week so not really sure I have the power yet to suggest purchasing third party software but we'll see what my boss says.
have a great day
-
Not a problem. I just like to be clear when I'm posting for one of our products.
It really is one I used to use all the time for the databases that I had to document. It was as simple as running it and then handing the .RTF file over to the auditors. They thought I had spent weeks prepping stuff.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
darth.pathos 62444 (12/13/2013)
Thanks Lowell, I'll have to review this a little more closely after I'm out of my meeting, but (and this may simply be a case of my missing it) but I don't see how to create the percentage nulls for each field and the min / max values. But as I said, I'll take a look in a bit.Thanks again 🙂
to do THAT, it requires a table scan for each table you want, so that's gonna be ugly and slow.
you'd use the metadata from sys.columns to generate the statements for each table...
offhand, it's something like this, right? I assume group by everything except the PK of the table(identity for some ?)
SELECT
MIN(ColumnName) As ColumnNameMin,
MAX(ColumnName) As ColumnNameMax,
SUM(CASE WHEN ColumnName IS NULL THEN 1 ELSE 0 END) As ColumnNameNulls,
SUM(CASE WHEN ColumnName IS NULL THEN 0 ELSE 1 END) As ColumnNameNotNulls,
MIN(ColumnName2) As ColumnName2Min,
MAX(ColumnName2) As ColumnName2Max,
SUM(CASE WHEN ColumnName IS NULL THEN 1 ELSE 0 END) As ColumnName2Nulls,
SUM(CASE WHEN ColumnName IS NULL THEN 0 ELSE 1 END) As ColumnName2NotNulls,
FROM YourTable
GROUP BY ColumnName,ColumnName2
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
this generates the basics of what you are asking; you'll need to further filter on the datatype of the columns(text,image,bit, others?); the first version i tested returned an error for example:
my assumption was to group by everything that is not the identity column in the table;
Msg 8117, Level 16, State 1, Line 1
Operand data type bit is invalid for min operator.
SELECT DISTINCT
t.name,
sq.Columns,
'SELECT '
+ sq.ColumnsMin
+ ','
+ sq.ColumnsMax
+ ','
+ sq.Columnsnulls
+ ','
+ sq.ColumnsNotNulls
+ ' FROM '
+ quotename(t.name)
+ ' GROUP BY '
+ sq.Columns
FROM sys.tables t
JOIN (SELECT
OBJECT_ID,
Columns = STUFF((SELECT ',' + name
FROM sys.columns sc
WHERE sc.object_id = s.object_id
FOR XML PATH('')),1,1,''),
ColumnsMin = STUFF((SELECT
',MIN(' + quotename(name) + ') As ' + + quotename(name + 'Min')
FROM sys.columns sc
WHERE sc.object_id = s.object_id
AND sc.is_identity = 0
FOR XML PATH('')), 1, 1, ''),
ColumnsMax = STUFF((SELECT
',MAx(' + quotename(name) + ') As ' + + quotename(name + 'Max')
FROM sys.columns sc
WHERE sc.object_id = s.object_id
AND sc.is_identity = 0
FOR XML PATH('')), 1, 1, ''),
ColumnsNulls = STUFF((SELECT
',SUM(CASE WHEN ' + quotename(name) + ' IS NULL THEN 1 ELSE 0 END) As ' + + quotename(name + 'Nulls')
FROM sys.columns sc
WHERE sc.object_id = s.object_id
AND sc.is_identity = 0
FOR XML PATH('')), 1, 1, ''),
ColumnsNotNulls = STUFF((SELECT
',SUM(CASE WHEN ' + quotename(name) + ' IS NULL THEN 0 ELSE 1 END) As ' + + quotename(name + 'NotNulls')
FROM sys.columns sc
WHERE sc.object_id = s.object_id
AND sc.is_identity = 0
FOR XML PATH('')), 1, 1, '')
FROM sys.columns s
WHERE s.is_identity = 0) sq
ON t.object_id = sq.object_id
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply