DECLARE @what varchar(800) SET @what='%Zs%'--要搜索的字符串 DECLARE @sqlStr varchar(8000) DECLARE TableCursor CURSOR LOCAL FOR SELECT sqlStr='IF EXISTS ( SELECT 1 FROM ['+o.name+'] WHERE ['+c.name+'] LIKE ''%'+@what+'%'' ) PRINT ''['+o.name+']''' FROM syscolumns c JOIN sysobjects o ON c.id=o.id WHERE o.xtype='U' AND c.status>=0 AND c.xusertype IN (175, 239, 231, 167 ) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @sqlStr WHILE @@FETCH_STATUS=0 BEGIN EXEC( @sqlStr ) FETCH NEXT FROM TableCursor INTO @sqlStr END CLOSE TableCursor DEALLOCATE TableCursor
public static DataSet GetDsFromSql(string sqlStr) { string strConnection = "...数据库连接正常..."; SqlConnection conn = new SqlConnection(strConnection); DataSet ds = new DataSet(); try { conn.Open(); SqlDataAdapter da = new SqlDataAdapter(sqlStr, conn); da.Fill(ds); } catch (Exception e) { ds.Tables.Add("TempTable"); } conn.Close(); return ds; }
#7
-- 建一个存储过程,然后你在代码里面调用这个存储过程就可以了 -- 返回多个表,每表仅有一行一列,里面的值就是你要的tablename create proc proc_GetTableName @column varchar(50), @value varchar(50) as begin declare @table varchar(50) declare cur cursor for select name from sysobjects where type = 'U' open cur fetch next from cur into @table while(@@fetch_status = 0) begin if(col_length(@table,@column) is not null) begin declare @sql nvarchar(4000) declare @result int set @sql = 'select @count = count(1) from '+@table+' where '+@column+' like ''%'+@value+'%''' exec sp_executesql @sql,N'@count int output',@result output if(@result > 0) begin select @table end end fetct next from cur into @table end close cur deallocate cur end
public static DataSet GetDsFromSql(string sqlStr) { string strConnection = "...数据库连接正常..."; SqlConnection conn = new SqlConnection(strConnection); DataSet ds = new DataSet(); try { conn.Open(); SqlDataAdapter da = new SqlDataAdapter(sqlStr, conn); da.Fill(ds); } catch (Exception e) { ds.Tables.Add("TempTable"); } conn.Close(); return ds; }
2楼的是print出来,所以你的代码接收不到.
思路是一样的,建立游标从系统表里面读出所有的tablename
select name from sysobjects where type = 'U'
然后遍历,判断表是否存在你指定的列
if(col_length(@table,@column) is not null)
最后,有指定列的表,就根据where column like '%'+value+'%'去读取是否有指定的指,
读取到之后select tablename出来.
2楼的是print出来,所以你的代码接收不到.
思路是一样的,建立游标从系统表里面读出所有的tablename
select name from sysobjects where type = 'U'
然后遍历,判断表是否存在你指定的列
if(col_length(@table,@column) is not null)
最后,有指定列的表,就根据where column like '%'+value+'%'去读取是否有指定的指,
读取到之后select tablename出来.
执行7楼代码 加 Exec proc_GetTableName 'Name','Zs' 吗?
#12
2楼的是print出来,所以你的代码接收不到.
思路是一样的,建立游标从系统表里面读出所有的tablename
select name from sysobjects where type = 'U'
然后遍历,判断表是否存在你指定的列
if(col_length(@table,@column) is not null)
最后,有指定列的表,就根据where column like '%'+value+'%'去读取是否有指定的指,
读取到之后select tablename出来.
select name, 'table1' from table1 where name = ‘Zs’ union all select name, 'table2' from table2 where name = ‘Zs’ union all select name, 'table3' from table3 where name = ‘Zs’
#15
1、获取所有数据库名
Select Name From Master..SysDatabases order By Name
2、获取所有表名 Select Name From SysObjects Where XType='U' order By Name
XType='U':表示所有用户表; XType='S':表示所有系统表;
3、.获取所有字段名 Select Name From SysColumns Where id=Object_Id(‘TableName’)
对所有表名的集合中,含有指定字段名的表做查询
直到找到你需要的表,或失败
你连过程化的查询都不清楚,就去套用不一定正确的存储过程,成功率是极低的
#16
别搞那么复杂,直接用下面的,有多少表写多少
select name, 'table1' from table1 where name = ‘Zs’ union all select name, 'table2' from table2 where name = ‘Zs’ union all select name, 'table3' from table3 where name = ‘Zs’
额,数据库的表是动态生成的,有n个
#17
1、获取所有数据库名
Select Name From Master..SysDatabases order By Name
2、获取所有表名 Select Name From SysObjects Where XType='U' order By Name
XType='U':表示所有用户表; XType='S':表示所有系统表;
3、.获取所有字段名 Select Name From SysColumns Where id=Object_Id(‘TableName’)
DECLARE @what varchar(800) SET @what='%Zs%'--要搜索的字符串 DECLARE @sqlStr varchar(8000) DECLARE TableCursor CURSOR LOCAL FOR SELECT sqlStr='IF EXISTS ( SELECT 1 FROM ['+o.name+'] WHERE ['+c.name+'] LIKE ''%'+@what+'%'' ) PRINT ''['+o.name+']''' FROM syscolumns c JOIN sysobjects o ON c.id=o.id WHERE o.xtype='U' AND c.status>=0 AND c.xusertype IN (175, 239, 231, 167 ) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @sqlStr WHILE @@FETCH_STATUS=0 BEGIN EXEC( @sqlStr ) FETCH NEXT FROM TableCursor INTO @sqlStr END CLOSE TableCursor DEALLOCATE TableCursor
public static DataSet GetDsFromSql(string sqlStr) { string strConnection = "...数据库连接正常..."; SqlConnection conn = new SqlConnection(strConnection); DataSet ds = new DataSet(); try { conn.Open(); SqlDataAdapter da = new SqlDataAdapter(sqlStr, conn); da.Fill(ds); } catch (Exception e) { ds.Tables.Add("TempTable"); } conn.Close(); return ds; }
#7
-- 建一个存储过程,然后你在代码里面调用这个存储过程就可以了 -- 返回多个表,每表仅有一行一列,里面的值就是你要的tablename create proc proc_GetTableName @column varchar(50), @value varchar(50) as begin declare @table varchar(50) declare cur cursor for select name from sysobjects where type = 'U' open cur fetch next from cur into @table while(@@fetch_status = 0) begin if(col_length(@table,@column) is not null) begin declare @sql nvarchar(4000) declare @result int set @sql = 'select @count = count(1) from '+@table+' where '+@column+' like ''%'+@value+'%''' exec sp_executesql @sql,N'@count int output',@result output if(@result > 0) begin select @table end end fetct next from cur into @table end close cur deallocate cur end
public static DataSet GetDsFromSql(string sqlStr) { string strConnection = "...数据库连接正常..."; SqlConnection conn = new SqlConnection(strConnection); DataSet ds = new DataSet(); try { conn.Open(); SqlDataAdapter da = new SqlDataAdapter(sqlStr, conn); da.Fill(ds); } catch (Exception e) { ds.Tables.Add("TempTable"); } conn.Close(); return ds; }
2楼的是print出来,所以你的代码接收不到.
思路是一样的,建立游标从系统表里面读出所有的tablename
select name from sysobjects where type = 'U'
然后遍历,判断表是否存在你指定的列
if(col_length(@table,@column) is not null)
最后,有指定列的表,就根据where column like '%'+value+'%'去读取是否有指定的指,
读取到之后select tablename出来.
2楼的是print出来,所以你的代码接收不到.
思路是一样的,建立游标从系统表里面读出所有的tablename
select name from sysobjects where type = 'U'
然后遍历,判断表是否存在你指定的列
if(col_length(@table,@column) is not null)
最后,有指定列的表,就根据where column like '%'+value+'%'去读取是否有指定的指,
读取到之后select tablename出来.
执行7楼代码 加 Exec proc_GetTableName 'Name','Zs' 吗?
#12
2楼的是print出来,所以你的代码接收不到.
思路是一样的,建立游标从系统表里面读出所有的tablename
select name from sysobjects where type = 'U'
然后遍历,判断表是否存在你指定的列
if(col_length(@table,@column) is not null)
最后,有指定列的表,就根据where column like '%'+value+'%'去读取是否有指定的指,
读取到之后select tablename出来.
select name, 'table1' from table1 where name = ‘Zs’ union all select name, 'table2' from table2 where name = ‘Zs’ union all select name, 'table3' from table3 where name = ‘Zs’
#15
1、获取所有数据库名
Select Name From Master..SysDatabases order By Name
2、获取所有表名 Select Name From SysObjects Where XType='U' order By Name
XType='U':表示所有用户表; XType='S':表示所有系统表;
3、.获取所有字段名 Select Name From SysColumns Where id=Object_Id(‘TableName’)
对所有表名的集合中,含有指定字段名的表做查询
直到找到你需要的表,或失败
你连过程化的查询都不清楚,就去套用不一定正确的存储过程,成功率是极低的
#16
别搞那么复杂,直接用下面的,有多少表写多少
select name, 'table1' from table1 where name = ‘Zs’ union all select name, 'table2' from table2 where name = ‘Zs’ union all select name, 'table3' from table3 where name = ‘Zs’
额,数据库的表是动态生成的,有n个
#17
1、获取所有数据库名
Select Name From Master..SysDatabases order By Name
2、获取所有表名 Select Name From SysObjects Where XType='U' order By Name
XType='U':表示所有用户表; XType='S':表示所有系统表;
3、.获取所有字段名 Select Name From SysColumns Where id=Object_Id(‘TableName’)