Avatar of curiouswebster
curiouswebsterFlag for United States of America

asked on 

Checking a SQL Server DB schema for Non-Normalized tables?

Checking a SQL Server DB schema for Non-Normalized tables?



I there an automated way to find tables which are not normalized? Or are there queries  which I can tailor to tell me which tables are not normalized?



Thanks.

DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
Dustin Saunders
ASKER CERTIFIED SOLUTION
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

No, no tool does that.

But the short answer is likely that none of the tables are properly normalized.  That sounds bad, but it's almost certainly true.  Typically tables are created by developers who give zero thought to normalization.  
SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of curiouswebster
curiouswebster
Flag of United States of America image

ASKER

Ok, a few queries and the SSMS graphical tool can help identify candidates for normalization. But, I now worry that a single normalization could break countless store procedures, since they create and query the non-normalized data.

I have used SQL Server automation tools which could certainly help in this situation.

For example, Red Hat has a tool I once used to identify all uses of a certain variable name, which saved huge amounts of time, since it looked through all database objects.

Any suggestions on whether SSMS does this, or what tools to consider?
SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of curiouswebster
curiouswebster
Flag of United States of America image

ASKER

Wow! You mean I can change the underlying schema to be more normalized, then use Views to handle legacy queries from both the source code and the Analysts, who interact with the database throughout the day?

If this is true, is there any benefit to normalization?

Don't get me wrong, organizationally, this may be a life-saver. Then, as time permits, we upgrade code and Analyst queries to the new schema.

In fact, we are considering giving the Analysts their own database schema.

How would a dedicated Analyst schema help or hurt this effort?
SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo