sql query to copy tables & stored procedures schema into another database

Hi,

Check this out:

Information Schema Views (Transact-SQL)
T-SQL: List of information_schema views

--Tables
SELECT * FROM INFORMATION_SCHEMA.TABLES

--Columns
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

--Stored procedures
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'

--Functions
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

You can use

SP_HELPTEXT Procedure_Or_Function_Name

to get the contents of the procedures and functions, there's maybe a better way though.

Hope it helps!