How to Generate a List of Tables in a SQL Database

I needed a list of tables in a database to create a script that would put an index on each of the tables. In SQL Server 2005 you can’t copy the list of tables in a database from the summary view. So how can you get a list of tables in text format? I turned to my favorite reference, Google, and didn’t find anything promising. So I decided to figure it out.

I happened to have a drop script in front of me, so I thought I would try using the sysobjects table. After running a simple Select statement on sysobjects, I noticed that all of the tables were there, along with key, etc. I also noticed a field named xtype. All of the user tables had the value “U” in that field. So I tried:

SELECT [name]
FROM [sysobjects]
WHERE [xtype] = 'U'

Yes, it was that simple.