March 18, 2010 at 6:41 am
Dear All,
Greetings for the day..!
My criteria is to script the Database completely (DB Schema -Tables, Procedures, Views) using T-SQL
and also Would like to automate this process (Job) in the off peak hours for alternate days to be saved to a folder with timestamp.
Other than direct scripting i would like to have T-SQL queries.
Any help and thoughts....
-Win.
Cheers,
- Win.
" Have a great day "
March 18, 2010 at 6:59 am
The only difficulty here is the tables because the views/functions/procedures can be gotten at through sys.views, sys.procedures, sys.sql_modules. So, I have only included something for the tables:
IF OBJECT_ID('Script_Table') IS NULL
EXEC('CREATE PROC Script_Table AS SELECT 0')
GO
ALTER PROC Script_Table
(
@TableName SYSNAME,
@IncludeConstraints BIT = 1,
@IncludeIndexes BIT = 1,
@NewTableName SYSNAME = NULL,
@UseSystemDataTypes BIT = 0
)
AS
BEGIN
DECLARE @MainDefinition TABLE
(
FieldValue VARCHAR(200)
)
DECLARE @DBName SYSNAME
DECLARE @ClusteredPK BIT
DECLARE @TableSchema NVARCHAR(255)
SET @DBName = DB_NAME(DB_ID())
SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)
DECLARE @ShowFields TABLE
(
FieldID INT IDENTITY(1,1),
DatabaseName VARCHAR(100),
TableOwner VARCHAR(100),
TableName VARCHAR(100),
FieldName VARCHAR(100),
ColumnPosition INT,
ColumnDefaultValue VARCHAR(100),
ColumnDefaultName VARCHAR(100),
IsNullable BIT,
DataType VARCHAR(100),
MaxLength INT,
NumericPrecision INT,
NumericScale INT,
DomainName VARCHAR(100),
FieldListingName VARCHAR(110),
FieldDefinition CHAR(1),
IdentityColumn BIT,
IdentitySeed INT,
IdentityIncrement INT,
IsCharColumn BIT
)
DECLARE @HoldingArea TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)
DECLARE @PKObjectID TABLE
(
ObjectID INT
)
DECLARE @Uniques TABLE
(
ObjectID INT
)
DECLARE @HoldingAreaValues TABLE
(
FldID SMALLINT IDENTITY(1,1),
Flds VARCHAR(4000),
FldValue CHAR(1) DEFAULT(0)
)
DECLARE @Definition TABLE
(
DefinitionID SMALLINT IDENTITY(1,1),
FieldValue VARCHAR(200)
)
INSERT INTO @ShowFields
(
DatabaseName,
TableOwner,
TableName,
FieldName,
ColumnPosition,
ColumnDefaultValue,
ColumnDefaultName,
IsNullable,
DataType,
MaxLength,
NumericPrecision,
NumericScale,
DomainName,
FieldListingName,
FieldDefinition,
IdentityColumn,
IdentitySeed,
IdentityIncrement,
IsCharColumn
)
SELECT
DB_NAME(),
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
CAST(ORDINAL_POSITION AS INT),
COLUMN_DEFAULT,
dobj.name AS ColumnDefaultName,
CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
DATA_TYPE,
CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
CAST(NUMERIC_PRECISION AS INT),
CAST(NUMERIC_SCALE AS INT),
DOMAIN_NAME,
COLUMN_NAME + ',',
'' AS FieldDefinition,
CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn,
CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed,
CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement,
CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
FROM
INFORMATION_SCHEMA.COLUMNS c
JOIN sys.columns sc ON c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'
WHERE c.TABLE_NAME = @TableName
ORDER BY
c.TABLE_NAME, c.ORDINAL_POSITION
SELECT TOP 1 @TableSchema = TableOwner
FROM @ShowFields
INSERT INTO @HoldingArea (Flds) VALUES('(')
INSERT INTO @Definition(FieldValue)
VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)
INSERT INTO @Definition(FieldValue)
VALUES('(')
INSERT INTO @Definition(FieldValue)
SELECT
CHAR(10) + FieldName + ' ' +
CASE
WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
ELSE UPPER(DataType) +
CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +
CASE WHEN IdentityColumn = 1 THEN ' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +
CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +
CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END
END +
CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END
FROM @ShowFields
IF @IncludeConstraints = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT
',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'
FROM
(
SELECT
ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name,
REVERSE(SUBSTRING(REVERSE((
SELECT cp.name + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)), 2, 8000)) ParentColumns,
REVERSE(SUBSTRING(REVERSE((
SELECT cr.name + ','
FROM
sys.foreign_key_columns fkc
JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE fkc.constraint_object_id = fk.object_id
FOR XML PATH('')
)), 2, 8000)) ReferencedColumns
FROM sys.foreign_keys fk
) a
WHERE ParentObject = @TableName
INSERT INTO @Definition(FieldValue)
SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints
WHERE OBJECT_NAME(parent_object_id) = @TableName
INSERT INTO @PKObjectID(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
OBJECT_NAME(parent_object_id) = @TableName AND
i.type = 1 AND
is_primary_key = 1
INSERT INTO @Uniques(ObjectID)
SELECT DISTINCT
PKObject = cco.object_id
FROM
sys.key_constraints cco
JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
OBJECT_NAME(parent_object_id) = @TableName AND
i.type = 2 AND
is_primary_key = 0 AND
is_unique_constraint = 1
SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END
INSERT INTO @Definition(FieldValue)
SELECT ',CONSTRAINT ' + name + CASE type WHEN 'PK' THEN ' PRIMARY KEY ' + CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END
WHEN 'UQ' THEN ' UNIQUE ' END + CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' +
REVERSE(SUBSTRING(REVERSE((
SELECT
c.name + + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.key_constraints ccok
LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
WHERE
i.object_id = ccok.parent_object_id AND
ccok.object_id = cco.object_id
FOR XML PATH('')
)), 2, 8000)) + ')'
FROM
sys.key_constraints cco
LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID
LEFT JOIN @Uniques u ON cco.object_id = u.objectID
WHERE
OBJECT_NAME(cco.parent_object_id) = @TableName
END
INSERT INTO @Definition(FieldValue)
VALUES(')')
IF @IncludeIndexes = 1
BEGIN
INSERT INTO @Definition(FieldValue)
SELECT
'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' + OBJECT_NAME(object_id) + '] (' +
REVERSE(SUBSTRING(REVERSE((
SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
FROM
sys.index_columns sc
JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
WHERE
OBJECT_NAME(sc.object_id) = @TableName AND
sc.object_id = i.object_id AND
sc.index_id = i.index_id
ORDER BY index_column_id ASC
FOR XML PATH('')
)), 2, 8000)) + ')'
FROM sys.indexes i
WHERE
OBJECT_NAME(object_id) = @TableName
AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1
AND is_unique_constraint = 0
AND is_primary_key = 0
END
INSERT INTO @MainDefinition(FieldValue)
SELECT FieldValue FROM @Definition
ORDER BY DefinitionID ASC
SELECT * FROM @MainDefinition
END
March 18, 2010 at 7:02 am
I forgot about the schemas which can be gotten from sys.schemas. Of course there are also the users and permissions, so let me know if you want a script for that too. You should write a cursor that goes through all the objects and scripts them one by one by object type.
March 18, 2010 at 7:16 am
SCPTXFR.EXE utility was available in SQL Server 2000. It is not available in 2005/2008. :hehe:
March 18, 2010 at 8:25 am
You could trace what the script generation wizard executes.
March 18, 2010 at 2:01 pm
You can use third party red-gate SQL packager.
Thanks,
Nikul
March 19, 2010 at 3:11 am
Thanks much Toby,
Yeah i need to script Schema as well..
Sorry did not posted.
Cheers,
- Win.
" Have a great day "
March 19, 2010 at 3:26 am
Toby,
T-SQL - given be you..
Can this be used to make this to run alternate days.. In a JOB, so that it can script the objects without data.
Cheers,
- Win.
" Have a great day "
March 19, 2010 at 7:23 am
A job can be run on any schedule you define. However, I am unclear what you want to do with the output. In any case, you probably want to write a stored procedure that iterates through all the object definitions and builds a Varchar(max) string for the entirety of the output. You could then store this output in a file or execute it on another database.
The stored procedure that does all this would be extensive. I would write it with one cursor per object type.
Let me know if you have more specific questions.
Regards,
Toby
March 19, 2010 at 7:36 am
if it HAS to be done in T-SQL, I've written a stored proc which returns the DDL of any table (or treats a view as if it were a table) in a nicely formatted output.
I've also got an example of scripting everything out in foreign key hierarchy order, all in TSQL as well.
it's about as full featured as I could make it, and takes into acount everything:
here's an example output of a fairly complex table: note it had indexes, a trigger, defaults, calculated columns, a rule, and more.
CREATE TABLE [dbo].[WHATEVER] (
[WHATEVERID] INT IDENTITY(2,5) NOT NULL,
[DESCRIP] VARCHAR(30) NULL,
[MYVARBINARY] VARBINARY NULL,
[MYBINARY] BINARY NULL
CONSTRAINT [DF__WHATEVER__mybina__6E01572D] DEFAULT ((42)),
[MYIMAGE] IMAGE NULL,
[MYVARCHAR] VARCHAR(1) NULL,
[MYMAXVARCHAR] VARCHAR(max) NULL,
[MYCHAR] CHAR(1) NULL
CONSTRAINT [DF__WHATEVER__mychar__6EF57B66] DEFAULT ('Y'),
[MYNVARCHAR] NVARCHAR(1) NULL,
[MYNCHAR] NCHAR(1) NULL,
[MYTEXT] TEXT NULL,
[MYNTEXT] NTEXT NULL,
[MYUNIQUEIDENTIFIER] UNIQUEIDENTIFIER NULL,
[MYROWVERSION] TIMESTAMP NOT NULL,
[MYBIT] BIT NULL,
[MYTINYINT] TINYINT NULL,
[MYSMALLINT] SMALLINT NULL,
[MYINT] INT NULL,
[MYBIGINT] BIGINT NULL,
[MYSMALLMONEY] SMALLMONEY NULL,
[MYMONEY] MONEY NULL,
[MYNUMERIC] NUMERIC(18,0) NULL,
[MYDECIMAL] DECIMAL(18,0) NOT NULL
CONSTRAINT [DF__WHATEVER__mydeci__70DDC3D8] DEFAULT ((0)),
[MYREAL] REAL(24) NULL,
[MYFLOAT] FLOAT NULL,
[MYSMALLDATETIME] SMALLDATETIME NULL,
[MYDATETIME] DATETIME NULL,
[MYCALCULATEDCOLUMN] AS (case when [mydatetime]<getdate() then 'Valid' else 'EXPIRED' end),
[MYSQL_VARIANT] SQL_VARIANT NULL,
[MYXML] XML NULL,
[INSERTDT] DATETIME NULL
CONSTRAINT [DF__WHATEVER__INSERT__71D1E811] DEFAULT (getdate()),
[UPDATEDDT] DATETIME NULL
CONSTRAINT [DF__WHATEVER__UPDATE__72C60C4A] DEFAULT (getdate()),
CONSTRAINT [PK__WHATEVER__6C190EBB] PRIMARY KEY CLUSTERED (WHATEVERID),
CONSTRAINT [UQ__WHATEVER__6D0D32F4] UNIQUE NONCLUSTERED (DESCRIP),
CONSTRAINT [FK__WHATEVER__myint__6FE99F9F] FOREIGN KEY (myint) REFERENCES WHATEVERREF(WHATEVERREFID))
GO
CREATE INDEX [IX_ANOTHERWHATEVER] ON [WHATEVER] (mytinyint, myvarchar)
CREATE INDEX [IX_WHATEVER] ON [WHATEVER] (mydatetime, myvarchar)
if not exists(SELECT [name] FROM sys.objects WHERE TYPE='R' AND schema_id = 1 AND [name] = '[range_rule]')
CREATE RULE range_rule
AS
@range>= $1000 AND @range <$20000;
GO
EXEC sp_binderule [WHATEVER], '[WHATEVER].[mymoney]'
GO
GO
CREATE TRIGGER TR_WHATEVER
ON WHATEVER
FOR INSERT,UPDATE
AS
UPDATE WHATEVER
SET UPDATEDDT = GETDATE()
FROM INSERTED
WHERE WHATEVER.WHATEVERID=INSERTED.WHATEVERID
Lowell
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply