To List all Columns and Tables in a database

/*************************************************************************/
/* 
STORED PROCEDURE 
~~~~~~~~~~~~~~~~
NAME            :  spro_ListAllColumns
PURPOSE         :  To LIST ALL COLUMNS AND TABLES IN A DATABASE
AUTHOR          :  ABHAY PATNY
EMAIL           :  sendtoabhay@yahoo.com
CREATED ON	:  JANUARY 22, 2002
LAST MODIFIED ON:  FEBRUARY 12,2002

PARAMETERS
~~~~~~~~~~
@dbname	--	Name of Database to be Searched
			Default is 'pubs'

EXAMPLES
~~~~~~~~

EXEC spro_ListAllColumns

EXEC spro_ListAllColumns 'mydatabase'

*/
CREATE PROC  spro_ListAllColumns

@dbname	VARCHAR (50) ='pubs'

AS

SET NOCOUNT ON

	DECLARE @tablename VARCHAR (50)
	DECLARE @fieldname VARCHAR(100),@type VARCHAR (20),@len INT
	DECLARE @temp VARCHAR(1000)

	-- Checking Whether th Database Exist or Not
	IF NOT EXISTS (SELECT name FROM master..sysdatabases WHERE name =@dbname) 
	BEGIN
		RAISERROR ('Database does not exist',16,1)
		RETURN -1
	END

	-- Checking Whether the user has Permissions for the Database
	IF HAS_DBACCESS (QUOTENAME(@dbname))=0
	BEGIN
		RAISERROR ('You DO NOT HAVE PERMISSIONS onn the Database',16,1)
		RETURN -1
	END
	
	PRINT '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
	PRINT 'List of All the Fields in Database : ' + UPPER(@dbname)
	PRINT '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'

	EXEC ('DECLARE cur2 CURSOR 	FOR select name from ' + @dbname + '..sysobjects where type=''U'' order by name')
	OPEN cur2
	FETCH NEXT FROM cur2 INTO @tablename
		WHILE (@@fetch_status <> -1)
		BEGIN
			PRINT ''
			PRINT 'TABLE : ' + @tablename 
			PRINT '~~~~~'
			SET @temp= 'DECLARE cur3 CURSOR FOR SELECT B.name as ''FIELD NAME'',C.name as ''DATA TYPE'' ,B.length as ''LENGTH'' FROM '+ @dbname +'..sysobjects A'
			SET @temp = @temp + ' INNER JOIN '+ @dbname +'..syscolumns B ON  A.id=B.id INNER JOIN '+ @dbname +'..systypes C ON B.xtype=C.xtype'
			SET @temp = @temp + ' WHERE A.type=''U'' and A.name=''' + @tablename + ''' ORDER BY B.name'

			EXEC (@temp)

			OPEN cur3
			FETCH NEXT FROM cur3 INTO @fieldname,@type,@len
				WHILE (@@fetch_status <> -1)
				BEGIN
					PRINT '          ' + @fieldname + '    [' + @type + ' (' + LTRIM(RTRIM(STR(@len))) + ')]'

				FETCH NEXT FROM cur3 INTO @fieldname,@type,@len
				END
			CLOSE cur3
			DEALLOCATE cur3
		FETCH NEXT FROM cur2 INTO @tablename
		END
	CLOSE cur2
	DEALLOCATE cur2
GO

/*************************************************************************/