今天老外有一个需求，要求将某一个用户下的所有表的创建脚本抓出来，并且每个表保存为一个文件，以下为脚本：
set feedback off
set pagesize 0
set heading off
set verify off
set linesize 200
set serveroutput on size 1000000
set trimspool on
define outputpath
accept outputpath char default ‘c:\’ prompt ‘pls input output path:’
define runfile = ‘c:\runfile.sql’
spool &runfile
prompt set feedback off
prompt set pagesize 0
prompt set heading off
prompt set verify off
prompt set linesize 500
prompt set trimspool on
prompt SET serveroutput on long 999999
prompt EXECUTE [...]

表结构：

演示数据如下：

写法一：
CREATE PROCEDURE UP_S_LocationTreeByLocationID
@LocationID VARCHAR(36)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Level INT
DECLARE @Temp TABLE(LocationID VARCHAR(36),ParentID VARCHAR(36),iLevel INT,LocationNm VARCHAR(40))
DECLARE @Stack TABLE(LocationID VARCHAR(36),iLevel INT)
SET @Level = 1
INSERT INTO @Temp
SELECT LocationID,
ParentLocationID,
@Level,
LocationNm
FROM tblLocation
WHERE LocationID = @LocationID
INSERT INTO @Stack VALUES(@LocationID,@Level)
WHILE @Level > 0
BEGIN
IF EXISTS(SELECT 1 FROM @Stack WHERE iLevel = @Level)
BEGIN
SELECT @LocationID = LocationID
FROM @Stack
WHERE iLevel = @Level
PRINT @LocationID
[...]