☆常用SQL脚本☆
/*CreateEntity*/
USE EasySales --数据库:EasySales
GO
Declare @MyEntity varchar(100)
Set @MyEntity = 'myAddrListEntity' --命名实体
Declare @MyDataTable varchar(100) --数据源[DataTable]
Set @MyDataTable = 'ds.Tables(0)'
Declare @MyTable varchar(100) --数据库表:AL_AddrListCataLog
Set @MyTable = 'AL_AddrListCataLog'
SELECT (case when a.colorder=1 then 'Public Const Table_' + d.name + ' As String = "' + d.name + '"' else '' end) 表映射类名,
'Public Const ' + UPPER(a.name) + ' AS String="' + (a.name) + '"' AS 字段映射常量,
case when b.name='int' then '.Add(Me.' + UPPER(a.name) + ' , GetType(System.Int64))'
when b.name='datetime' then '.Add(Me.' + UPPER(a.name) + ' , GetType(System.DateTime))'
else '.Add(Me.' + UPPER(a.name) + ' , GetType(System.String))' end
+ case a.isnullable when 0 then '' else '.AllowDBNull = True' end,
'.Rows(i).Item(' + @MyEntity + '.' + UPPER(a.name) + ') = ' + @MyDataTable + '.Rows(i).Item(' + @MyEntity + '.' + UPPER(a.name) + ')',
b.name 字段类型, a.isnullable
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id AND a.colid = g.smallid
where d.name=@MyTable
order by a.id,a.colorder
---------------------------------------
/*联结字段*/
DECLARE @AAA VARCHAR(8000)
SET @AAA=''
select @AAA=@AAA+
CASE a.isnullable WHEN 1 THEN
'Isnull([' + a.name + '],' +
Case b.name When 'int' Then '0' When 'bit' Then '0' When 'tinyint' Then '0' When 'money' Then '0.00' When 'datetime' Then '''1900-1-1''' When 'uniqueidentifier' Then '''00000000-0000-0000-0000-000000000000''' Else '''''' End
+ ') AS ['+a.name+ '], '
ELSE
'['+ a.name + '], '
END
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name=@MyTable --数据库表[AL_AddressList]
order by a.id
PRINT @AAA
USE EasySales --数据库:EasySales
GO
Declare @MyEntity varchar(100)
Set @MyEntity = 'myAddrListEntity' --命名实体
Declare @MyDataTable varchar(100) --数据源[DataTable]
Set @MyDataTable = 'ds.Tables(0)'
Declare @MyTable varchar(100) --数据库表:AL_AddrListCataLog
Set @MyTable = 'AL_AddrListCataLog'
SELECT (case when a.colorder=1 then 'Public Const Table_' + d.name + ' As String = "' + d.name + '"' else '' end) 表映射类名,
'Public Const ' + UPPER(a.name) + ' AS String="' + (a.name) + '"' AS 字段映射常量,
case when b.name='int' then '.Add(Me.' + UPPER(a.name) + ' , GetType(System.Int64))'
when b.name='datetime' then '.Add(Me.' + UPPER(a.name) + ' , GetType(System.DateTime))'
else '.Add(Me.' + UPPER(a.name) + ' , GetType(System.String))' end
+ case a.isnullable when 0 then '' else '.AllowDBNull = True' end,
'.Rows(i).Item(' + @MyEntity + '.' + UPPER(a.name) + ') = ' + @MyDataTable + '.Rows(i).Item(' + @MyEntity + '.' + UPPER(a.name) + ')',
b.name 字段类型, a.isnullable
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id AND a.colid = g.smallid
where d.name=@MyTable
order by a.id,a.colorder
---------------------------------------
/*联结字段*/
DECLARE @AAA VARCHAR(8000)
SET @AAA=''
select @AAA=@AAA+
CASE a.isnullable WHEN 1 THEN
'Isnull([' + a.name + '],' +
Case b.name When 'int' Then '0' When 'bit' Then '0' When 'tinyint' Then '0' When 'money' Then '0.00' When 'datetime' Then '''1900-1-1''' When 'uniqueidentifier' Then '''00000000-0000-0000-0000-000000000000''' Else '''''' End
+ ') AS ['+a.name+ '], '
ELSE
'['+ a.name + '], '
END
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name=@MyTable --数据库表[AL_AddressList]
order by a.id
PRINT @AAA
/*查询字段在数据库各表中信息*/
Declare @fieldStr as varchar(200)
Set @fieldStr = 'Product_ID' --给字段字符串赋值[Product_ID为字段名称]
SELECT o.[name] as tname, c.[name] as fname, c.length, t.[name] as type
FROM dbo.sysobjects o inner join dbo.syscolumns c
on o.id=c.id and c.[name] in (@fieldStr)
left join dbo.systypes t on c.xtype=t.xusertype
Where o.xtype='U' and o.status>0
-- And (t.[name] = 'int')
Order By o.[name]
---------------------------------------
/*定义变量*/
Declare @MyTable varchar(100) --数据库表:News
Set @MyTable = 'News'
SELECT
'Private _' + (a.name) + ' AS '+
case when b.name='int' then 'integer'
when b.name='tinyint' then 'Int16'
when b.name='bit' then 'byte'
when b.name='image' then 'Byte()'
when b.name='datetime' then 'DateTime'
when b.name='smalldatetime' then 'DateTime'
when b.name='uniqueidentifier' then 'GUID'
when b.name='char' then 'int16'
when b.name='money' then 'Decimal'
else 'String' end 定义变量,
b.name 字段类型, a.isnullable
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id AND a.colid = g.smallid
where d.name=@MyTable
order by a.id,a.colorder
---------------------------------------
/*定义属性*/
Declare @MyTable varchar(100) --数据库表:News
Set @MyTable = 'News'
Declare @myProperty AS varchar(8000)
select @myProperty=''
SELECT @myProperty = @myProperty+'Public Property ' + (a.name) + '() AS '+
case when b.name='int' then 'integer'
when b.name='tinyint' then 'Int16'
when b.name='bit' then 'byte'
when b.name='image' then 'Byte()'
when b.name='datetime' then 'DateTime'
when b.name='smalldatetime' then 'DateTime'
when b.name='uniqueidentifier' then 'GUID'
when b.name='char' then 'int16'
when b.name='money' then 'Decimal'
else 'String' end
+ CHAR(10) + ' Get'+ CHAR(10) + ' Return _' + (a.name) + CHAR(10) + ' End Get'+ CHAR(10) +
' Set(ByVal Value As '
+ case when b.name='int' then 'integer'
when b.name='tinyint' then 'Int16'
when b.name='bit' then 'byte'
when b.name='image' then 'Byte()'
when b.name='datetime' then 'DateTime'
when b.name='smalldatetime' then 'DateTime'
when b.name='uniqueidentifier' then 'GUID'
when b.name='char' then 'int16'
when b.name='money' then 'Decimal'
else 'String' end + ')' + char(10)
+ ' me._' + (a.name) + ' = Value'+ CHAR(10) +
' End Set'+ CHAR(10) + 'End Property'+ CHAR(10)
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id AND a.colid = g.smallid
where d.name=@MyTable
order by a.id,a.colorder
Print @myProperty
---------------------------------------
Declare @fieldStr as varchar(200)
Set @fieldStr = 'Product_ID' --给字段字符串赋值[Product_ID为字段名称]
SELECT o.[name] as tname, c.[name] as fname, c.length, t.[name] as type
FROM dbo.sysobjects o inner join dbo.syscolumns c
on o.id=c.id and c.[name] in (@fieldStr)
left join dbo.systypes t on c.xtype=t.xusertype
Where o.xtype='U' and o.status>0
-- And (t.[name] = 'int')
Order By o.[name]
---------------------------------------
/*定义变量*/
Declare @MyTable varchar(100) --数据库表:News
Set @MyTable = 'News'
SELECT
'Private _' + (a.name) + ' AS '+
case when b.name='int' then 'integer'
when b.name='tinyint' then 'Int16'
when b.name='bit' then 'byte'
when b.name='image' then 'Byte()'
when b.name='datetime' then 'DateTime'
when b.name='smalldatetime' then 'DateTime'
when b.name='uniqueidentifier' then 'GUID'
when b.name='char' then 'int16'
when b.name='money' then 'Decimal'
else 'String' end 定义变量,
b.name 字段类型, a.isnullable
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id AND a.colid = g.smallid
where d.name=@MyTable
order by a.id,a.colorder
---------------------------------------
/*定义属性*/
Declare @MyTable varchar(100) --数据库表:News
Set @MyTable = 'News'
Declare @myProperty AS varchar(8000)
select @myProperty=''
SELECT @myProperty = @myProperty+'Public Property ' + (a.name) + '() AS '+
case when b.name='int' then 'integer'
when b.name='tinyint' then 'Int16'
when b.name='bit' then 'byte'
when b.name='image' then 'Byte()'
when b.name='datetime' then 'DateTime'
when b.name='smalldatetime' then 'DateTime'
when b.name='uniqueidentifier' then 'GUID'
when b.name='char' then 'int16'
when b.name='money' then 'Decimal'
else 'String' end
+ CHAR(10) + ' Get'+ CHAR(10) + ' Return _' + (a.name) + CHAR(10) + ' End Get'+ CHAR(10) +
' Set(ByVal Value As '
+ case when b.name='int' then 'integer'
when b.name='tinyint' then 'Int16'
when b.name='bit' then 'byte'
when b.name='image' then 'Byte()'
when b.name='datetime' then 'DateTime'
when b.name='smalldatetime' then 'DateTime'
when b.name='uniqueidentifier' then 'GUID'
when b.name='char' then 'int16'
when b.name='money' then 'Decimal'
else 'String' end + ')' + char(10)
+ ' me._' + (a.name) + ' = Value'+ CHAR(10) +
' End Set'+ CHAR(10) + 'End Property'+ CHAR(10)
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id AND a.colid = g.smallid
where d.name=@MyTable
order by a.id,a.colorder
Print @myProperty
---------------------------------------