How to systematically manage a Big List of Queries and Tables in SQL Server ?

Suppose someone has to work on a lot of different SQL Server Databases which have got a lot of Tables and Queries / Views inside them.

After a period of time, it becomes very difficult to remember exactly what kind of columns are present within a given Table and View.

Please suggest some method by which one can keep a systematic list of all the Tables and Views that are present within a SQL Server Database, along with the columns that are present within them.
Are there any Add-on products or services etc. available that helps in making this type of work systematic?

Currently I add comments to each queries inside SQL Server to remind me of what this query is doing, but this method is not great. I am looking for some better and more efficient methods.

Please share any ideas that you might have in this direction.

Thanks a lot
happy 1001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Define 'systematically manage'.  

All tables and columns allow you to add a description that can be used for self-document.

Pragmatic Works has a Doc xPress tool where the $395 version is a relative point-and-click that documents a given database / SSAS / SSIS / SSRS as html/rtf/word docs.  The $1,295 version also allows you to add extra 'love notes' a la a business-facing data dictionary plus multiple versions for different roles.

I'm sure there are other products out there, I'm just not familar with them.  

Good luck.
Vitor MontalvãoMSSQL Senior EngineerCommented:
After a period of time, it becomes very difficult to remember exactly what kind of columns are present within a given Table and View.
Unless you are working only with a single database and the database has very few tables and views then no one can remember everything in a database.

Please suggest some method by which one can keep a systematic list of all the Tables and Views that are present within a SQL Server Database, along with the columns that are present within them.
There are products that keeps all information about a database model. You can also use the Diagrams option in MSSQL Management Studio.

Currently I add comments to each queries inside SQL Server to remind me of what this query is doing,
How do you keep queries inside SQL Server? Are you talking about Views and Stored Procedures? Anyway, comments are always welcome since you don't know if in a few years you'll need to make a change in the query and having all objects commented it will help you to understand the table/view/query/etc... in the future.
happy 1001Author Commented:
Thanks Jim
I will check out Doc xPress tool.
---------------------------------

Thanks for your reply Victor.
There are products that keeps all information about a database model. You can also use the Diagrams option in MSSQL Management Studio.

Please name a few of such products. I am looking for them.

How do you keep queries inside SQL Server? Are you talking about Views and Stored Procedures?

Mostly I keep the queries in the form of SQL codes within a .sql file, where I add comments under each such query, giving some details. But this method is not at all efficient, as the SQL file grows very long and it becomes hard to manage, therefor I was looking for some better alternatives.

Thanks
Vitor MontalvãoMSSQL Senior EngineerCommented:
Please name a few of such products. I am looking for them.
I like Erwin from CA. I also heard very good comments about Embarcadero.


Mostly I keep the queries in the form of SQL codes within a .sql file
Ok, you are using script files.
But this method is not at all efficient, as the SQL file grows very long and it becomes hard to manage,
Do you know the Template Browser option in SSMS (menu View)? You can organize your script files from there. Check the image below to have an ideia of the kind of the organization you can have with this option:
TemplateBrowser.PNG

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
happy 1001Author Commented:
Thanks Victor, that was quite helpful. I was not aware of this option earlier.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.