Refreshing the SP to a database facilitates the production of XSD files

Data | database | refresh
----------------------------------------------------------------------------------
--Refreshing SP to database--
--                    --
--&Old& Source database name--
--&New& Target database name--
--                    --
--The table name in the target database is the user-defined SP, FN, etc. in the source database (you can add it by prompting-----
--The column name in the table is its argument--
--                    --
--Huang Zongban--
--2005.01.19--
----------------------------------------------------------------------------------
ALTER PROCEDURE dbo. P_ref
As
DECLARE @SQL nvarchar (4000)

--The database does not exist to create
IF not EXISTS (
SELECT [Name]
From master.dbo.sysdatabases
WHERE [Name] = ' &New& '
)
BEGIN
CREATE DATABASE &New&
End

--Remove SP, FN, TF its name, ID
DECLARE @Tbl CURSOR
SET @Tbl = CURSOR local SCROLL for
SELECT [name], [ID]
From &old&.dbo.sysobjects
--To add refresh type please modify here
WHERE ([name] like ' p% ' or [name] like ' f% ' or [name] like ' tf% ')
and (type = ' P ' or type = ' FN ' or type = ' TF ')

DECLARE @TblName nvarchar (100)
DECLARE @TblID int

--Create a table with the name @tblname
OPEN @Tbl
FETCH NEXT from @Tbl into @TblName, @TblID
while (@ @FETCH_STATUS = 0)
BEGIN
--The table already exists deletes
IF EXISTS
(
SELECT [name] from &new&.dbo.sysobjects
WHERE [Name] = @TblName
and type = ' U '
)
BEGIN
SET @SQL = ' DROP TABLE ' + ' &New& ' + '. dbo. ' + @TblName
EXEC sp_executesql @SQL
IF (@ @ERROR <> 0)
BEGIN
RAISERROR (' Delete existing table%s ' failed! ', one, 1, @TblName)
Return
End
End

--Skip if no arguments
IF (SELECT Count (*) from Dbo.syscolumns WHERE [name] like ' @% ' and [id] = @TblID) = 0)
BEGIN
FETCH NEXT from @Tbl into @TblName, @TblID
CONTINUE
End

--Remove column names and their types
DECLARE @Col CURSOR
SET @Col = CURSOR local SCROLL for
SELECT &old&.dbo.syscolumns. [Name], &old&.dbo.systypes. [Name]
From &old&.dbo.syscolumns left OUTER JOIN
&old&.dbo.systypes on &old&.dbo.syscolumns.xtype = &old&.dbo.systypes.xtype
WHERE &old&.dbo.syscolumns. [Name] Like ' @% '
and &old&.dbo.syscolumns. [id] = @TblID
ORDER BY &old&.dbo.syscolumns.colorder

DECLARE @ColName nvarchar (50)
DECLARE @ColType nvarchar (20)

--Constructing SQL statements
SET @SQL = ' CREATE TABLE &new&.dbo. ' + @TblName + ' ('
OPEN @Col
FETCH NEXT from @Col into @ColName, @ColType
DECLARE @ColNameLast nvarchar (50)
SET @ColNameLast = '
while (@ @FETCH_STATUS = 0)
BEGIN
SET @ColName = SubString (@ColName, 2, Len (@ColName)-1)
--Skipping duplicate columns
IF (@ColName <> @ColNameLast)
BEGIN
SET @SQL = @SQL + @ColName + ' + @ColType + ', '
SET @ColNameLast = @ColName
End
FETCH NEXT from @Col into @ColName, @ColType
End
SET @SQL = SubString (@SQL, 1, Len (@SQL)-1)
SET @SQL = @SQL + ') '

--Execute SQL statement
EXEC sp_executesql @SQL
IF (@ @ERROR <> 0)
BEGIN
RAISERROR (' Create table%s ' failed! ', one, 1, @TblName)
Return
End

--Create the next table
FETCH NEXT from @Tbl into @TblName, @TblID
End

return @ @ERROR