Script all user database tables …

--Script all user database tables.

set nocount on
use
master
go
declare @DBNAME varchar(128)
declare @TabName varchar(128)
declare @TabID int
declare @DDLDEF VARCHAR(8000)
declare @ColName varchar(1000)
declare @Count int
declare @FileGroup varchar(128)
declare DBCur cursor
for
select name from sysdatabases where name not in ('tempdb', 'master', 'pubs', 'Northwind', 'msdb', 'model')
order by name
OPEN DBCur
fetch next from DBCur into @DBNAME
while @@FETCH_STATUS =0

begin
exec('use ' + @DBNAME)
PRINT '/*Scripting for ' + @DBNAME + ' done on ' + CAST(GetDate() as varchar(50)) + '*/'
declare TabNameCur cursor
for
select '[' + su.name + '].[' + so.name + ']', so.id , sfg.groupname
from sysusers su inner join sysobjects so
on su.uid = so.uid
inner join sysindexes si on si.id = so.id
inner join sysfilegroups sfg on sfg.groupid = si.groupid
where so.type = 'U'
order by su.name, so.name
open TabNameCur
fetch next from TabNameCur into @TabName, @TabID, @FileGroup
while @@FETCH_STATUS = 0
begin
set @DDLDef = Char(13) + 'CREATE TABLE ' + @TabName + '(' + Char(13)
/* Must incorporate all datatypes */
declare ColNameCur cursor
for
select '[' + sc.name + ']' + ' ' + st.name +
CASE when st.name = 'char' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'nchar' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'nvarchar' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'varbinary' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'binary' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'varchar' then '(' + CAST(sc.LENGTH as varchar(50)) + ')'
when st.name = 'decimal' then '(' + CAST(sc.LENGTH as varchar(50)) + ','
+ CAST(sc.PREC as varchar(50)) + ')'
when st.name = 'numeric' then '(' + CAST(sc.LENGTH as varchar(50)) + ',' +
CAST(sc.PREC as varchar(50)) + ')'
else ''
end
+ ' ' +
CASE when sc.ISNULLABLE = 1 then 'NULL' else 'NOT NULL' end
from syscolumns sc
INNER
JOIN systypes st on sc.xtype = st.xtype
where sc.id = @TabID
order by sc.colid
open ColNameCur
fetch next from ColNameCur into @ColName
set @Count = 1
while @@FETCH_STATUS = 0 -- ColNameCur
begin
if @Count = 1
begin
set @DDLDef = @DDLDef + ' ' + @ColName + Char(13)
set @Count = @Count + 1
end
else
begin
set @DDLDef = @DDLDef + ' , ' + @ColName + Char(13)
set @Count = @Count + 1
end
fetch next from ColNameCur into @ColName
end
/* ColNameCur @@FetchStatus */
set @DDLDef = @DDLDef + ') ON [' + @FileGroup + ']' + Char(13) + 'GO'
PRINT @DDLDEF
set @DDLDef = ''
close ColNameCur
deallocate ColNameCur
fetch next from TabNameCur into @TabName, @TabID, @FileGroup
end
close TabNameCur
deallocate TabNameCur
fetch
next
from DBCur into @DBNAME
end
close DBCur
deallocate DBCur