Found a Tool Very Helpful for Beginner
-
I struggled with remembering the conventions I'd chosen when creating column names and data types. Likewise I struggle - continually - with relationships and keeping track of Foreign Key assignments. While I have not fully resolved the last issue, I stumbled upon a query that will list all the data elements in a database along with attributes assigned:
USE hutchinsCLAN;
SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],
T.[name] AS [table_name], AC.[name] AS [column_name],
TY.[name] AS system_data_type, AC.[max_length],
AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded]
FROM sys.[tables] AS T
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]
WHERE T.[is_ms_shipped] = 0
ORDER BY T.[name], AC.[column_id]
For other noobs, know that the only change needed to the query is the database name, shown bold/italic above. I've found this global view of the database helpful, perhaps because my roots are in flat file design; but I find it useful for db design, too.
I copied the results and pasted them into an Excel spreadsheet. I've added comments and other information to the spreadsheet. I found this very helpful for finding where the conventions for naming, datatype and length weren't being used. I found names that I wanted to change,etc. I changed the spreadsheet cells and highlighted them. I then split my screen into 2 windows placing the spreadsheet in one and SSMS in the other thus making it easy to make changes in table values. As I made the table changes I used a different cell color value to keep up with changes. The ss below doesn't include all data elements, but you get he idea. I shaded the background for system tables to remind myself not to change them. I added the column headings and adjusted column width. The query gives information in columns from Schema through Is ANSI Padded.
I'm now trying to use the spreadsheet to identify relationships. I added columns to it for tracking FKs and the tables they relate to. I haven't found a one-to-many situation yet but can add columns for that as needed. I also copied the query into the spreadsheet and added my conventions so I have a quick reference.
I submit this in the hope that it will give other neophytes and idea or 2.
If this sort of post in inappropriate, please tell me.
- This topic was modified 1 month ago by Ahr Aitch. Reason: corrections and additional info
- This topic was modified 1 month ago by Ahr Aitch. Reason: to highlight last sentence intended for admins
Be kind. Be calm. Be generous. Behave.
-
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
The post is fine and appropriate. It also introduces some of the meta-data available about tables and a couple of things.
I have a couple of questions for you, though....
- It may be a stupid question on my part but I need to make sure... Do you know what the "Object Explorer" window is in SSMS? If not, then with SSMS open, press the F8 key once and it'll open up.
- Have you tried using sp_Help ?
- Although it's not superb, do you have "Intelisense" enabled in SSMS?
- Remembering that it's a free tool and so isn't "uber sophisticated", if you click on the pull-down for a database in the Object Explorer, there's table diagramming tool available that I use a fair bit. It's not for writing queries but it can make for a quick little ERD diagram.
https://www.mssqltips.com/sqlservertip/6269/sql-server-database-diagram-tool-in-management-studio/
There are also some interesting "settings" to be made in SSMS like what you can include in the results when you generate scripts for tables, etc.
While I have an appreciation for the tool that you found, there's just no need to maintain a separate spreadsheet for such simple information. Most all of it and more available right from SSMS.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks for the response, Jeff.
In answer to your questions:
1. Yes, I know about and have used Object Explorer in SSMS. I created my databsase and its tables using it; however, there is much to be learned about the mysteries it contains. I've not yet reached that tenth of a tenth of a percent mark yet. I'm yet to find a good wiki on SSMS; so please let me know if there is one.
2. No, I have not used sp_help. I did a quick scan of the information at the link. It will take a bit more concentration and application to suss its use for me.
3. I have no knowlege of 'Intelisense'. I don't even know how to see if it's installed. That's another rabbit to chase.
4. Again, I have to ask you forebearance with my ignorance. You mention a pull-down for a databs in OE and refernce a table diagramming tool. Are you referring to Database Diagrams? If so, I've sample the fare there but only a little. I learned enough to be able to add datatypes to the table boxes and the 'nullable' info for each column to the displayed tables. I've struggles trying to make relationships using the tool, though. I learned to highlight the column in the child table and drag/drop it on the column in the parent table, but the relationships that result aren't clear. The lines that are created do not connect to the proper lines on the drawing and when hovered over, do not show the details that I thought had been chosen. Worse, relationships thus made don't persist after leaving the diagrams window despite every effort I've made to save them before exiting. I'm having similar, but different, problems writing queries to set Foreign Key (FK) constraints.
As for my op, it is my total lack of skill with SSMS and SQL that makes the export-to-Excel approach helpful. As I said, I found the query that lists all the columns while looking for something else. The spreadsheet is not a permanent solution, for sure; but it allows me to have a broad view of the database that I cannot have table by table. I have no doubt that there are other, better tools. I look forward to learning enough to use them in place of the spreadsheet. I posted the tip because I thought there might be others struggling in the dark and barking their shins on hard objects who might be able to use it for the moment, just as I have.
Thanks for the links. I'll give them a thorough going over.
I'm still open to good tools for learning how to exploit SSMS' many features that I know so l
Be kind. Be calm. Be generous. Behave.
-
I appreciate your honest replies and your professionalism in wanting to help others as you learn. That's pretty much the spirit for most of the long term denizens of this forum and a lot of the new folks, as well.
I don't have any recommendations for good, free tutorials on SSMS because I've never used one and I've never found an "easy good one" from Microsoft. I learned a long time ago when it was "Enterprise Manager" and just sort of grew with it as new revs came out. I'm not an expert at it but I manage to get the things I need to do, done with it. If I need to do something, I'll search for a couple of keywords. Lately, I might ask an AI that also returns links to some of the sources it used (my favorite so far is perplexity.ai).
That, not withstanding, I did do such a search and found some YouTube s on the subject that are quite long and had a clickable "table of contents" that seem to follow a decent order of revelation. Here's the search I used... hopefully someone else reading this has a direct recommendation.
https://duckduckgo.com/?q=SSMS+Tutorial
I'm sure you've already run across this in the Object Explorer but when you drill down into "Databases", each database will have at least the following first level of drill-downs.
In the following, I've expanded the drill-downs to the table and column level just so you can see that it does provide column info. If you right click on tables, you can filter the names of the tables shown when you have a lot of tables.
It also shows you the "Intellisense" icon. It's worth looking at a short tutorial for the best way to use it but it will try to make "auto-complete" suggestions for things like column and object names.
And then there's the other stuff of selecting a schema and object name (you only need object name if it's unique across schemas) in your code and then pressing Alt-F1. It makes a handy summary of a lot of different things, especially when it comes to tables.
In the object explorer, you can also right click on objects and tell them to generate the code for that object. And, for tables, you can setup what gets returned by (100,000 foot introduction) clicking on <Tools><Options><SQL Server Object Explorer><Scripting> to setup to include triggers, indexes, keys, constraints, etc, etc ,when you script a table. There's also a wealth of other settings in that same window (below).
I hope this helps a bit more in your "starting efforts". It's a pretty big subject.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, again, I cannot thank you enough for this kind of help. I truly appreciate the time and effort you put into helping me.
You've given me lot to chew on. I shall take advantage of the YouTube links, especially. Maybe with a bit better understanding of SSMS I can make better use of the detail you pointed out.
Thank you, again.
Be kind. Be calm. Be generous. Behave.
-
Jeff, I've spent a few hours watching YouTube videos on SSMS since reading your response. I had tried that when I first got SSMS installed and working a bit for me, but the presentation rates were too fast or the material too foreign to my ken that I couldn't stick with them. Today, I was actually able to get through the ones I watched without bailing on them. I didn't understand everything presented, but I have a better base now for watching them and will return from time to time.
I had purchased several books on the subject but find reading tedious after having the dynamism of YouTube and other online tutorials.
Thanks for reminding me to return to them. I'm still chewing on information in your post, but shall persevere. .
Be kind. Be calm. Be generous. Behave.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply