--Just copy and paste the following script in SQL Server Query Analyser and modify database name from 1st line.
USE
[DATABASE_NAME] -- Put your database Name here before
execute
Go
-- Don't Modify
any portion from here if you don't understand the coding.
IF EXISTS(SELECT * FROM sys.objects WHERE
type_desc LIKE '%FUNCTION%'
AND name='TableScript') BEGIN
DROP FUNCTION
[dbo].[TableScript]
END
GO
CREATE FUNCTION [dbo].[TableScript]
(@TableName VARCHAR(max)=null) RETURNS @sql table(s varchar(1000), id int identity)
AS
BEGIN
DECLARE @table VARCHAR(100)
SET @table = @TableName -- set table
name here
-- create
statement
INSERT INTO @sql(s) VALUES ('create table [' +
@table + '] (')
-- column list
INSERT INTO @sql(s)
SELECT
' ['+column_name+'] ' +
data_type +
COALESCE('('+CAST(character_maximum_length
AS VARCHAR)+')','') + ' ' +
CASE WHEN EXISTS (
SELECT
id FROM syscolumns
WHERE object_name(id)=@table
and
name=column_name
and columnproperty(id,name,'IsIdentity') = 1
) THEN
'IDENTITY('
+
CAST(ident_seed(@table) AS VARCHAR) + ',' +
CAST(ident_incr(@table) AS VARCHAR) + ')'
ELSE ''
END + ' ' +
( CASE WHEN IS_NULLABLE
= 'No' THEN 'NOT ' ELSE '' END ) + 'NULL ' +
COALESCE('DEFAULT '+COLUMN_DEFAULT,'') + ','
FROM information_schema.columns WHERE
table_name = @table
ORDER BY ordinal_position
-- primary key
DECLARE @pkname VARCHAR(100)
SELECT @pkname = constraint_name FROM
information_schema.table_constraints
WHERE table_name
= @table and
constraint_type='PRIMARY
KEY'
IF ( @pkname is not null ) BEGIN
INSERT INTO @sql(s) VALUES(' PRIMARY KEY (')
INSERT INTO @sql(s)
SELECT ' ['+COLUMN_NAME+'],' FROM information_schema.key_column_usage
WHERE
constraint_name = @pkname
ORDER BY ordinal_position
-- remove
trailing comma
UPDATE @sql
SET s=left(s,len(s)-1) WHERE id=@@identity
INSERT INTO @sql(s) values (' )')
END
ELSE BEGIN
-- remove
trailing comma
UPDATE @sql
SET s=left(s,len(s)-1) WHERE id=@@identity
END
-- closing
bracket
INSERT INTO @sql(s) VALUES( ')' )
-- result!
RETURN
END
GO
IF EXISTS(SELECT * FROM sys.objects WHERE
type_desc LIKE '%FUNCTION%'
AND name='RelationScript') BEGIN
DROP FUNCTION
[dbo].[RelationScript]
END
GO
CREATE FUNCTION [dbo].[RelationScript]
(@TableName varchar(max)=null) RETURNS @sql table(s varchar(1000))
AS
BEGIN
DECLARE @fkname VARCHAR(100)
SELECT @fkname = constraint_name FROM
information_schema.table_constraints
WHERE table_name
= @TableName and
constraint_type='FOREIGN
KEY'
IF ( @fkname is not null ) BEGIN
DECLARE
@TempTable TABLE (ForeignKey
VARCHAR(100),TableName VARCHAR(100),ColumnName VARCHAR(100),ReferenceTableName VARCHAR(100),ReferenceColumnName
VARCHAR(100))
INSERT INTO @TempTable
SELECT f.name AS ForeignKey,OBJECT_NAME(f.parent_object_id) AS TableName,COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,OBJECT_NAME (f.referenced_object_id)
AS ReferenceTableName,COL_NAME(fc.referenced_object_id,fc.referenced_column_id)
AS ReferenceColumnName
FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS
fc ON f.OBJECT_ID = fc.constraint_object_id WHERE
f.parent_object_id =
object_id(@TableName)
DECLARE
@No INT, @i INT
SET @i=0
SET @No=(SELECT COUNT(*) FROM @TempTable)
DECLARE
@mTable TABLE(t
VARCHAR(4000))
WHILE @i<@No BEGIN
DECLARE
@ForeignKeyName VARCHAR(100),@ColName VARCHAR(100),@RefTableName VARCHAR(100),@RefColName VARCHAR(100)
SET
@ForeignKeyName=(SELECT
TOP(1) ForeignKey FROM
@TempTable)
SET
@ColName=(SELECT
TOP(1) ColumnName FROM
@TempTable)
SET
@RefColName=(SELECT
TOP(1) ReferenceColumnName FROM
@TempTable)
SET
@RefTableName=(SELECT
TOP(1) ReferenceTableName FROM
@TempTable)
INSERT
INTO @mTable(t) SELECT ' ALTER TABLE ' +@TableName
+ ' WITH CHECK ADD CONSTRAINT ' +@ForeignKeyName
+ ' FOREIGN KEY '
+' ('+ @ColName +') REFERENCES '+@RefTableName
+' ('+ @RefColName +')'
INSERT
INTO @mTable(t) SELECT 'GO'
DELETE
FROM @TempTable WHERE
ForeignKey=@ForeignKeyName
SET
@i=@i+1
END
END
-- inserting
foreing keys alter statements
INSERT INTO @sql(s) SELECT t FROM @mTable
RETURN
END
GO
-- GENERATING
THE TABLE SCRIPT
DECLARE
@TableNames TABLE(Tname
VARCHAR(100))
DECLARE @TableName
VARCHAR(100)
DECLARE @Result TABLE(Script nText)
DECLARE @No INT,@i INT
INSERT INTO @Result(Script) SELECT '-- =============================================='
INSERT INTO @Result(Script) SELECT '-- Author:
Ghulam Moinuddin'
INSERT INTO @Result(Script) SELECT '-- Create date: 30- Aug -2012'
INSERT INTO @Result(Script) SELECT '-- Description:
Scripting All the database tables'
INSERT INTO @Result(Script) SELECT '-- =============================================='
INSERT INTO @Result(Script) SELECT ''
INSERT INTO @TableNames SELECT
name FROM sys.tables
SET @No=(SELECT COUNT(*) FROM @TableNames)
SET @i=0
WHILE @i<@No BEGIN
SET
@TableName =(SELECT
TOP(1) Tname FROM
@TableNames)
INSERT INTO @Result(Script) SELECT '--=============================================='
INSERT INTO @Result(Script) SELECT '-------- '+ CONVERT(VARCHAR(3),@i+1) +'. '+ @TableName+' ============='
INSERT INTO @Result(Script) SELECT '--=============================================='
INSERT INTO @Result(Script) SELECT s FROM dbo.TableScript(@TableName)
INSERT INTO @Result(Script) SELECT 'GO'
DELETE FROM @TableNames WHERE
Tname=@TableName
SET @i=@i+1
END
-- GENETATING
RELATION SCRIPT
DECLARE @Fname VARCHAR(100)
INSERT INTO @TableNames SELECT
name FROM sys.tables
SET @No=(SELECT COUNT(*) FROM @TableNames)
SET @i=0
WHILE @i<@No BEGIN
SET
@TableName =(SELECT
TOP(1) Tname FROM
@TableNames)
select
@Fname = constraint_name from information_schema.table_constraints
where
table_name = @TableName and constraint_type='FOREIGN KEY'
IF @Fname
IS NOT NULL BEGIN
IF
@i=0 BEGIN
INSERT
INTO @Result(Script) SELECT '-------------- FOREIGN KEY RELATION
--------------------------------'
END
INSERT
INTO @Result(Script) SELECT s FROM dbo.RelationScript(@TableName)
END
DELETE FROM @TableNames WHERE
Tname=@TableName
SET @i=@i+1
END
IF EXISTS(SELECT * FROM sys.objects WHERE
type_desc LIKE '%FUNCTION%'
AND name='TableScript') BEGIN
DROP FUNCTION
[dbo].[TableScript]
END
IF EXISTS(SELECT * FROM sys.objects WHERE
type_desc LIKE '%FUNCTION%'
AND name='RelationScript') BEGIN
DROP FUNCTION
[dbo].[RelationScript]
END
SELECT Script FROM @Result
--INSERT INTO @Result(Script)
SELECT '-------------- Stored Procedures --------------------------------'
