List of SQL table names and their field names

Some developers may want to get the list of table names and their field names, without scripting the database and at the same time they would like to copy the data to a new database with maybe new names and etc...
The script will provide the list of table and its fields and will insert into a the new db

Instruction:
· Change à SET @NewDBName = 'NewDB’ and rename to the new database name
· Find “First and second format” for the format that you require (or you can use the other variable that may help you to provide the query that you need such as the “@NewFieldName” , “@FieldName”)
· Run script
· Copy the results in to a new query page
· Run script

DECLARE @FieldName AS nVarChar(max)
DECLARE @NewDBName AS nVarChar(100)
DECLARE @NewFieldName AS nVarChar(max)
DECLARE @NewFieldString AS nVarChar(max)

SET @NewDBName = 'NewDB'

SET @FieldName = ''
SET @NewFieldName = ''
SET @NewFieldString = ''

DECLARE @tblName AS nVarChar(100)
DECLARE @object_ID AS int

DECLARE LoopCursor CURSOR FOR
SELECT tbl.Name , tbl.object_ID
FROM sys.objects AS tbl
WHERE tbl.TYPE = 'u' and tbl.name = 'users'
ORDER BY tbl.Name

OPEN LoopCursor
FETCH NEXT FROM LoopCursor INTO @tblName , @object_ID

IF @@FETCH_STATUS <> 0
PRINT ' <>'

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @FieldName = @FieldName + '[' + Cast (Col.Name AS nVarChar) + '], '
, @NewFieldName= @NewFieldName + '[' + Cast (Col.Name AS nVarChar) + '_NEW], '
, @NewFieldString = @NewFieldString
+ '[' + Cast (Col.Name AS nVarChar) + '] AS ' + '[' + Cast (Col.Name AS nVarChar) + '_NEW], '
FROM sys.columns Col
WHERE Col.object_ID = @object_ID
SET @NewFieldString = Left (@NewFieldString , Len(@NewFieldString) - 1 )
----------------------------------------------------------------------------------------------
----------------------------------------------------
-- First format ----------------------------------
-- PRINT ' SELECT ' + @NewFieldString
-- + ' INTO '+ '[' + @NewDBName + '].dbo.[' + @tblName + '_NEW]'
-- + ' FROM [' + @tblName + ']'
----------------------------------------------------
-- Second format ----------------------------------
PRINT ' SELECT ' + @FieldName
+ ' INTO '+ '[' + @NewDBName + '].dbo.[' + @tblName + ']'
+ ' FROM [' + @tblName + ']'
----------------------------------------------------
----------------------------------------------------------------------------------------------
FETCH NEXT FROM LoopCursor INTO @tblName , @object_ID

;
END
;
CLOSE LoopCursor
;
DEALLOCATE LoopCursor
;