IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'FN_GetTableStructure' AND Type = 'TF') AND SUSER_SNAME() = 'sa'
DROP FUNCTION FN_GetTableStructure
GO
CREATE FUNCTION FN_GetTableStructure(
@TabName sysname /*表名称*/
)
RETURNS @RT TABLE(
[ID] INTEGER IDENTITY(1,1),
[Column_name] sysname,
[Description] sql_variant,
[Type] sql_variant,
[Computed] sql_variant,
[Length] sql_variant,
[Prec] sql_variant,
[Scale] sql_variant,
[Nullable] sql_variant,
[TrimTrailingBlanks] sql_variant,
[FixedLenNullInSource] sql_variant,
[Collation] sql_variant)
AS
BEGIN
DECLARE @numtypes NVARCHAR(80)
DECLARE @TMP TABLE( [ID] INTEGER IDENTITY(1,1),
[FID] INTEGER,
[DN] sql_variant
)
select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'
INSERT INTO @RT([Column_name] ,
[Description] ,
[Type] ,
[Computed] ,
[Length] ,
[Prec] ,
[Scale] ,
[Nullable] ,
[TrimTrailingBlanks] ,
[FixedLenNullInSource] ,
[Collation] )
select
'Column_name' = C.name,
'Description' = V.Value,
'Type' = type_name(C.xusertype),
'Computed' = case when C.iscomputed = 0 then 'no' else 'yes' end,
'Length' = convert(int, C.length),
'Prec' = case when charindex(type_name(C.xtype), @numtypes) > 0
then convert(char(5),ColumnProperty(C.id, C.name, 'precision'))
else ' ' end,
'Scale' = case when charindex(type_name(C.xtype), @numtypes) > 0
then convert(char(5),OdbcScale(C.xtype,xscale))
else ' ' end,
'Nullable' = case when isnullable = 0 then 'no' else 'yes' end,
'TrimTrailingBlanks' = case ColumnProperty(2099048, C.name, 'UsesAnsiTrim')
when 1 then 'no'
when 0 then 'yes'
else '(n/a)' end,
'FixedLenNullInSource' = case
when type_name(C.xtype) not in ('varbinary','varchar','binary','char')
Then '(n/a)'
When C.status & 0x20 = 0 Then 'no'
Else 'yes' END,
'Collation' = C.collation
from syscolumns AS C
LEFT OUTER JOIN
(
SELECT *
FROM ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', @TabName, 'column', default)
) AS V
ON C.name = V.ObjName
where C.id = OBJECT_ID(@TabName) and C.number = 0 order by C.colid
INSERT INTO @TMP([FID],[DN])
SELECT R.ID,R.[Description]
FROM @RT AS R INNER JOIN
(SELECT [Description]
FROM @RT
WHERE [Description] IS NOT NULL
AND [Description] <> ''
GROUP BY [Description]
HAVING count(1) > 1
) AS A
ON R.[Description] = A.[Description]
WHERE R.[Description] IS NOT NULL
AND R.[Description] <> ''
ORDER BY 2,1
UPDATE @RT
SET [Description] = LTRIM(RTRIM(CAST([Description] AS NVARCHAR)))
+ CAST(A.SeqNo AS NVARCHAR)
FROM (SELECT ID + 1 - ISNULL((SELECT MIN(A.ID) FROM @TMP AS A WHERE A.DN = DN),0) AS SeqNo,
FID
FROM @TMP
WHERE 1=1 ) AS A
WHERE A.FID = ID
RETURN
END
GO
GRANT ALL ON FN_GetTableStructure TO PUBLIC
GO
IF @TFlag <> 1 SELECT @TFlag = 0
IF @FFlag <> 1 SELECT @FFlag = 0
SELECT @SQL_CODE1 = N'IF EXISTS(SELECT 1 FROM sysobjects WHERE name = ''View_$1'' AND Type = ''V'') DROP VIEW [View_$1] ',
@SQL_CODE2 = N'CREATE VIEW [View_$1] WITH ENCRYPTION AS SELECT $2 FROM [$3] WHERE 1=1',
@SQL_CODE3 = N'GRANT SELECT ON [View_$1] TO PUBLIC',
@SQL_COLUMN_STR = N'DECLARE SEL_COLUMN_CS CURSOR FOR '
+ N' SELECT CAST(Column_Name AS NVARCHAR) AS CName, '
+ N' CAST(CASE WHEN ([Description] IS NOT NULL AND [Description] <> '''') THEN [Description] '
+ N' ELSE (CASE WHEN $2 = 1 THEN [Column_Name] ELSE '''' END) '
+ N' END AS NVARCHAR) AS VName, '
+ N' CAST(Type AS NVARCHAR) AS Type '
+ N' FROM FN_GetTableStructure('''+'$1'+''')',
@FIELD_STR --= N' CASE WHEN UPPER(LTRIM(RTRIM(''$T''))) = ''FLOAT'' '
--+ N' THEN CAST($1 AS DECIMAL(18,6)) '
--+ N' WHEN UPPER(LTRIM(RTRIM(''$T''))) = ''NVARCHAR'' '
--+ N' THEN CAST($1 AS VARCHAR(8000)) '
--+ N' ELSE [$1] '
--+ N' END AS [$2] '
= N' [$1] AS [$2] ',
@FLOAT_FIELD_STR = N'CAST([$1] AS DECIMAL(18,6)) AS [$2]'
DECLARE SEL_TABLE_CS CURSOR FOR
SELECT O.Name AS TableName,
CAST(CASE WHEN D.Value IS NOT NULL AND D.Value <> '' THEN D.Value
ELSE (CASE WHEN @TFlag = 1 THEN O.Name ELSE '' END)
END AS NVARCHAR) AS ViewName
FROM sysobjects O LEFT OUTER JOIN fn_GetTabDescription(default) AS D ON O.Name = D.ObjName
WHERE (O.Name = @TableName OR @TableName IS NULL OR @TableName = '')
AND D.Name = 'MS_Description' AND O.Type = 'U'
ORDER BY 2 DESC
OPEN SEL_TABLE_CS
FETCH NEXT FROM SEL_TABLE_CS INTO @TableName,@ViewName
WHILE (@@FETCH_STATUS = 0) BEGIN
IF @ViewName IS NOT NULL OR @ViewName <> '' BEGIN
SELECT @SQL_STR = REPLACE(REPLACE(@SQL_COLUMN_STR,'$1',@TableName),'$2',@FFlag)
PRINT @SQL_STR
EXECUTE SP_EXECUTESQL @SQL_STR
OPEN SEL_COLUMN_CS
FETCH NEXT FROM SEL_COLUMN_CS INTO @CName,@VName,@Type
SELECT @SEL_FIELD = ''
WHILE (@@FETCH_STATUS = 0) BEGIN
IF @VName IS NOT NULL AND @VName <> '' BEGIN
--IF UPPER(LTRIM(RTRIM(@Type))) = 'FLOAT' BEGIN
-- IF @SEL_FIELD IS NOT NULL AND @SEL_FIELD <> '' SELECT @SEL_FIELD = LTRIM(RTRIM(@SEL_FIELD))+' ,'
-- SELECT @SEL_FIELD = LTRIM(RTRIM(@SEL_FIELD))+LTRIM(RTRIM(REPLACE(REPLACE(@FLOAT_FIELD_STR,'$1',@CName),'$2',@VName)))
--END ELSE BEGIN
IF @SEL_FIELD IS NOT NULL AND @SEL_FIELD <> '' SELECT @SEL_FIELD = LTRIM(RTRIM(@SEL_FIELD))+' ,'
--SELECT @SEL_FIELD = LTRIM(RTRIM(@SEL_FIELD))+LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@FIELD_STR,'$1',@CName),'$2',@VName),'$T',@Type)))
SELECT @SEL_FIELD = LTRIM(RTRIM(@SEL_FIELD))+LTRIM(RTRIM(REPLACE(REPLACE(@FIELD_STR,'$1',@CName),'$2',@VName)))
--END
END
FETCH NEXT FROM SEL_COLUMN_CS INTO @CName,@VName,@Type
END
CLOSE SEL_COLUMN_CS
DEALLOCATE SEL_COLUMN_CS
IF @SEL_FIELD IS NOT NULL AND @SEL_FIELD <> '' BEGIN
SELECT @SQL_STR = REPLACE(LTRIM(RTRIM(@SQL_CODE1)),'$1',@ViewName)
PRINT @SQL_STR
EXECUTE SP_EXECUTESQL @SQL_STR
SELECT @SQL_STR = REPLACE(LTRIM(RTRIM(@SQL_CODE3)),'$1',@ViewName)
PRINT @SQL_STR
EXECUTE SP_EXECUTESQL @SQL_STR
END
END
FETCH NEXT FROM SEL_TABLE_CS INTO @TableName,@ViewName
END
CLOSE SEL_TABLE_CS
DEALLOCATE SEL_TABLE_CS
END
GO
GRANT ALL ON ITP_CREATE_VIEW TO PUBLIC
GO
--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(aa),@rplen=len(@s_str),@postion=charindex(@s_str,aa)-1 from #tb
while @postion>0
begin
updatetext #tb.aa @p @postion @rplen @d_str
select @postion=charindex(@s_str,aa)-1 from #tb
end