Report of fields and data types

Question

Post reply

Report of fields and data types

vavfam 50321

Mr or Mrs. 500

Points: 511
More actions
March 19, 2015 at 8:38 am

#295267

I am starting a project to create the beginnings of MDM for my company. We have multiple related databases with inconsistent naming conventions and fields which need to be managed at both our level and at our client level. I wanted to create a report that would show the table, field names and data types using SSRS. I didn't know if this is possible or if I need to use something else to generate the information. I am using SQL Server 2012.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

well, all the information's in the metadata;

here's something i use myself, where i check if there are any extended property descriptions as well.

does this get you started?

select

--objz.object_id,

objz.name As TableName,

ISNULL(tabz.value,'') As TableDescription,

colz.name as ColumnName,

colz.column_id,

type_name(colz.system_type_id) As [DataType],

colz.is_computed As [Computed],

CASE When colz.max_length = -1 THEN '(max)' ELSE convert(varchar,colz.max_length) END As [Length],

colz.precision As [Precision],

colz.scale,

colz.is_nullable As [Nullable],

colz.is_identity As [IsUnique/Primary],

ISNULL(desz.value,'') As[ ColumnDescription],

CASE WHEN refs.object_id IS NOT NULL THEN ' FK To ' + object_name(refs.object_id) ELSE '' END As [FKReference],

'' As Notes

from sys.objects objz

inner join sys.columns colz on objz.object_id = colz.object_id

LEFT join sys.extended_properties tabz ON objz.object_id = tabz.major_id and minor_id=0

LEFT join sys.extended_properties desz ON colz.object_id = desz.major_id and colz.column_id = desz.minor_id

LEFT join sys.foreign_key_columns fks ON colz.object_id = fks.parent_object_id and colz.column_id = fks.parent_column_id

LEFT JOIN sys.columns refs ON fks.referenced_object_id = refs.object_id and fks.referenced_column_id = refs.column_id

--WHERE objz.name IN ('Employee','Facility')

order by objz.name,colz.column_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!

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

There's several System views you can query to get that type of information, for example:

INFORMATION_SCHEMA.TABLES

INFORMATION_SCHEMA.COLUMNS

sys.tables

sys.columns

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]