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

@dbnameVARCHAR (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

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