List All Tables and Columns in a Database

This is a query I created to show all the tables in a database and all of the columns on those tables (the columns also show their type, not including things like max length). If you run this in SSMS and output the results to text rather than to a grid, you can just paste it into Excel. You'll get one table per row, and one field per column when you paste into Excel.

-- Optional (may already be set by SQL connection.
USE NameOfYourDatabase
GO

-- Parameters.
DECLARE @DatabaseName AS varchar(max)
SET @DatabaseName = 'NameOfYourDatabase'

-- Variables.
DECLARE @TabChar AS varchar(1)
DECLARE @Name AS varchar(256)
DECLARE @Column AS varchar(256)
DECLARE @Type AS varchar(256)
DECLARE @Columns AS varchar(max)
DECLARE @Tables AS table(info varchar(max))

-- Cursor to query table names.
DECLARE TableCursor CURSOR FOR
SELECT
	name
FROM sys.Tables
WHERE
	type_desc = 'USER_TABLE'
ORDER BY
	name ASC

-- Loop through each table name.
OPEN TableCursor
GOTO FetchNextTable
WHILE @@FETCH_STATUS = 0
BEGIN
	
	-- Variables.
	SET @TabChar = ''
	SET @Columns = ''
	
	-- Cursor to query column info.
	DECLARE ColumnCursor CURSOR FOR
	SELECT
		Column_Name, Data_Type
	FROM information_schema.columns
	WHERE
		Table_Catalog = @DatabaseName
		AND Table_Name = @Name
	ORDER BY
		Ordinal_Position ASC
	
	-- Loop through each column.
	OPEN ColumnCursor
	GOTO FetchNextColumn
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Columns = @Columns + @TabChar + @Column + ' AS ' + @Type
		SET @TabChar = CHAR(9)
		FetchNextColumn:
		FETCH NEXT FROM ColumnCursor INTO @Column, @Type
	END
	CLOSE ColumnCursor
	DEALLOCATE ColumnCursor
	
	-- Store the table name and column information.
	INSERT INTO @Tables(info) VALUES (@Name + @TabChar + @Columns)
	
	-- Next table.
	FetchNextTable:
	FETCH NEXT FROM TableCursor INTO @Name
	
END
CLOSE TableCursor
DEALLOCATE TableCursor

-- Show all table information.
SELECT * FROM @Tables

Notice that there is a variable near the top that you'll need to change to match the name of your database. Also, if you include the command (at the top) to specify the database, you'll have to change that to your database name too.