MSSQL: Get List of Tables with Columns

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

CREATE FUNCTION [dbo].[fx_tables_and_columns] () RETURNS TABLE AS RETURN ( SELECT * FROM ( SELECT TOP 1000 a.TABLE_NAME AS f_table, (SELECT STUFF((SELECT ',' + CONVERT(VARCHAR(MAX),COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = a.TABLE_NAME ORDER BY ORDINAL_POSITION ASC FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')) AS f_columns FROM MyElitePortal.INFORMATION_SCHEMA.Tables a WHERE a.TABLE_TYPE = 'BASE TABLE' ORDER BY a.TABLE_NAME ) T )