July 9, 2009 at 2:24 pm
I know I can use my SQL Server Management studio to view the files and columns in a newly created database, but is there a way to generate a quick printed list of all the files and columns within each file? (sql server 2005)
Thanks in advance!
unix to sql newbie
July 9, 2009 at 7:35 pm
sandi5980 (7/9/2009)
I know I can use my SQL Server Management studio to view the files and columns in a newly created database, but is there a way to generate a quick printed list of all the files and columns within each file? (sql server 2005)Thanks in advance!
unix to sql newbie
Are you talking about the physical database files? If so, what do you mean by columns?
Or are you talking about the tables in the database, and the columns in those tables?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 9, 2009 at 7:42 pm
If you want the physical database files:
select [Logical Name] = [name],
[Physical Name] = physical_name
from sys.database_files
if you want the tables and columns in a database:
select [SchemaName] = schema_name(st.schema_id),
[TableName] = st.Name,
[ColumnName] = sc.name
from sys.columns sc
INNER JOIN sys.tables st ON st.object_id = sc.object_id
where st.is_ms_shipped = 0
order by SchemaName, TableName, column_id
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 13, 2009 at 10:35 am
Thanks Wayne.
This helped a little, but I am getting the info for my system db, not my production database. What I am looking for is exactly what you have in the 2nd part of your answer but I'm not getting the right database. Were do I substitute my "production" database?
July 14, 2009 at 5:21 pm
Sounds like you are running the query against one of the system databases such as 'master'.
Make sure that you run the query against the database whose tables and columns you want returned. The drop-down box near the top left corner of the SSMS window (on my PC) should contain a list of available databases that you can select from - select your production database, then run the query
🙂
July 14, 2009 at 5:46 pm
Use {your production database name here};
Go
select [SchemaName] = schema_name(st.schema_id),
[TableName] = st.Name,
[ColumnName] = sc.name
from sys.columns sc
INNER JOIN sys.tables st ON st.object_id = sc.object_id
where st.is_ms_shipped = 0
order by SchemaName, TableName, column_id;
You can either change the database context using the drop-down, or you can execute the USE command to USE the appropriate database.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2009 at 12:10 pm
Thank you, this is working better. From here is there anything I can add to directly print out the results from the query?
July 15, 2009 at 12:21 pm
Not sure what you mean by directly printing the results. You can always just right-click on the results pane and select print to print the results from a query.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2009 at 12:31 pm
Ok, I think my problem is that I don't have a direct way to print from the server.
Thank you again.
July 15, 2009 at 1:08 pm
sandi5980 (7/15/2009)
Ok, I think my problem is that I don't have a direct way to print from the server.Thank you again.
I see, you are remoting into the server and using SSMS on the server to run the query. Do you have SSMS on your desktop? If so, you can run it from there and print. If not, you can use Excel to connect to SQL Server using Microsoft Query.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 15, 2009 at 1:14 pm
Great idea, I'm going to see if I can get it installed on my pc. Sure would make things easier. But I'm going to try the excel anyhow & see what I get.
Thank you!
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply