I'm not convinced I've understood your question fully (or if I have I'm not convinced that it's a good thing to do). But here is some code that will query every table in your database - I've indicated where you would need to put the text for your script. Warning - this could take a long time to run.
DECLARE @SQLString nvarchar (255),
@ParmDefinition nvarchar (255)
DECLARE @tablename sysname, @Empty char (1)
DECLARE FindTables CURSOR READ_ONLY FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES WITH(NOLOCK)
WHERE TABLE_TYPE = 'BASE TABLE'
--AND TABLE_NAME NOT LIKE 'XXX%' -- Code any exclusions here
AND TABLE_SCHEMA = 'dbo'
ORDER BY TABLE_NAME
-- Note that if you want to narrow this down to columns in tables look at
-- select COLUMN_NAME, TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
OPEN FindTables
FETCH NEXT FROM FindTables INTO @tablename
WHILE (@@fetch_status = 0)
BEGIN
-- This is the line you would need to change into whatever your "script" needs to be
SET @SQLString = N'(SELECT * FROM [' + @tablename + '] WITH(NOLOCK))'
SET @ParmDefinition = N'@tablename sysname'
RAISERROR(@tablename, 0, 1) WITH NOWAIT
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@tablename = @tablename
FETCH NEXT FROM FindTables INTO @tablename
END
CLOSE FindTables
DEALLOCATE FindTables
I've re-read your question and what I
think you were really after was a way of generating a script that would recreate your entire database schema rather than all of the data in which case have a look at this tutorial here...
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/[
^]
[Edit - 2nd point confirmed by OP so removed original solution]
Submit an article or tip