SQL Management Studio for a beginner

Question

I'm new to this program. The software our company uses as our database has terrible reporting capabilities. I was just given access to the database (please forgive me if I use the wrong terminology) and to SQL Management Studio to perform queries and pull data out of our system. I'm very familiar with our database, but not with how it's structured in regards to tables, columns and data points. So I'm trying to figure out what the tables and columns are so I can actually try to start a query. I know WHAT I want to pull and I know WHERE it is in our software, but I don't know much more than that. I think if I could pull something that showed me what my table options were, along with what columns were available from each table, I could probably figure it out from there. Is there a way to pull available tables and what columns are tied to each table?

Answer 1

To get a list of all tables, you can run:

   SELECT s.name AS "schema", t.name AS "table"  
   FROM   sys.schemas s  
   JOIN     sys.tables t ON s.schema_id = t.schema_id  
   ORDER BY "schema", "table"  

To view the columns for a table, you can run

   EXEC sp_help 'schema.table'  

You can also use Object Explorer in SSMS to browse tables and columns. Object Explorer opens by default when you start SSMS.

To get a graphic illustration, you can create a database diagram, but you should do this in production. Expand the database in Object Explorer and select Database Diagrams and then Install Diagram Support before you add new diagram. These diagrams can be helpful if you have a smaller amount of tables, but with too many, it may be more confusing than helpful.

Yet an option when you are in query window is to right-click and select Design Query in Editor. This opens the Query Designer. This tool can be helpful for absolute beginners, but think of it as something you should grow out of by time, since it has a lot of limitations.

Answer 2

Hi @Sizemore, Shelly ,

You can use below T-SQL to get list of all tables in a database.

use database name  
  
SELECT   
    *  
FROM  
    information_schema.tables  
order by TABLE_SCHEMA;  

You can use below T-SQL to get column names from a table.

select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE  
from INFORMATION_SCHEMA.COLUMNS  
where TABLE_NAME='tableName';  

You can also get this information from SSMS UI as below.

For the relationship between tables of a database, suggest you use Database Diagrams (Visual Database Tools) as Erland mentioned, please refer to below blog to get more information.

SQL Server Database Diagram Tool in Management Studio

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".