DECLARE @string VARCHAR(MAX)

SET @string = ''

SELECT @string = @string + 'EXEC ( ''sp_helptext ' + name + ' '')' from sys.sysobjects where xtype in('p','tr','v') order by Xtype

EXEC ( @string )

and for tables use

http://www.stormrage.com/Portals/0/SSC/sp_GetDDL2005_V306.txt

the usage is simple:

exec sp_getDDL tablename

or

exec sp_getDDL 'schemaname.tablename'

Refer

http://www.sqlservercentral.com/scripts/SQL+Server+2005/67515/

I have used the above queries to fetch data from single database, for each database try with sp_MSForEachDB where you can use each and every database

Thanks

Parthi