冰天雪地 裸体 跪求sql

[gul_gui]

小弟在做微软project sever的二次开发，由于需要（任务状态改变用户改变了，但项目经理还没审批，这个数据不知道保存在哪）

想求一个通用的sql：

查出数据库中 所有表 任何字段的值等于某个数值的数据
（比如 A表 有 a b c 三个字段 select * from A where a='13' or b='13' or c='13'）

谢谢各位了

[--小F--]

------------------第一种方法----------------------

CREATE PROC sp_ValueSearch
@value sql_variant,  --要搜索的数据
@precision bit=1     --1=仅根据sql_variant中的数据类型查找对应类型的数据列.<>1,查询兼容的所有列,字符数据使用like匹配
AS
SET NOCOUNT ON
IF @value IS NULL RETURN

--数据类型处理
SELECT xtype INTO #t FROM systypes
WHERE name=SQL_VARIANT_PROPERTY(@value,N'BaseType')

--扩展数据类型及查询处理语句
DECLARE @sql nvarchar(4000),@sql1 nvarchar(4000)
IF @precision=1
    SET @sql=CASE SQL_VARIANT_PROPERTY(@value,N'BaseType')
        WHEN N'text' THEN N' LIKE N''%''+CAST(@value as varchar(8000))+''%'''
        WHEN N'ntext' THEN N' LIKE ''%''+CAST(@value as nvarchar(4000))+''%'''
        ELSE N'=@value' END
ELSE
BEGIN
    SET @sql=CAST(SQL_VARIANT_PROPERTY(@value,N'BaseType') as sysname)
    IF @sql LIKE N'%char' or @sql LIKE N'%text'
    BEGIN
        INSERT #t SELECT xtype FROM systypes
        WHERE name LIKE N'%char' or name LIKE N'%text'
        SELECT @sql=N' LIKE N''%''+CAST(@value as '
            +CASE 
                WHEN LEFT(@sql,1)=N'n' THEN ' nvarchar(4000)'
                ELSE 'varchar(8000)' END
            +N')+N''%'''
    END
    ELSE IF @sql LIKE N'%datetime'
    BEGIN
        INSERT #t SELECT xtype FROM systypes
        WHERE name LIKE N'%datetime'
        SET @sql=N'=@value'    
    END
    ELSE IF @sql LIKE N'%int' 
        OR @sql LIKE N'%money' 
        OR @sql IN(N'real',N'float',N'decimal',N'numeric')
    BEGIN
        INSERT #t SELECT xtype FROM systypes
        WHERE name LIKE N'%int' 
            OR name LIKE N'%money' 
            OR name IN(N'real',N'float',N'decimal')
        SET @sql=N'=@value'    
    END
    ELSE
        SET @sql=N'=@value'
END
--保存结果的临时表
CREATE TABLE #(TableName sysname,FieldName sysname,Type sysname,SQL nvarchar(4000))

DECLARE tb CURSOR LOCAL
FOR
SELECT N'SELECT * FROM '
    +QUOTENAME(USER_NAME(o.uid))
    +N'.'+QUOTENAME(o.name)
    +N' WHERE '+QUOTENAME(c.name)
    +@sql,
    N'INSERT # VALUES(N'+QUOTENAME(o.name,N'''')
    +N',N'+QUOTENAME(c.name,N'''')
    +N',N'+QUOTENAME(QUOTENAME(t.name)+CASE 
        WHEN t.name IN (N'decimal',N'numeric')
        THEN N'('+CAST(c.prec as varchar)+N','+CAST(c.scale as varchar)+N')'
        WHEN t.name=N'float'
            OR t.name like N'%char'
            OR t.name like N'%binary'
        THEN N'('+CAST(c.prec as varchar)+N')'
        ELSE N'' END,N'''')
    +N',@sql)'
FROM sysobjects o,syscolumns c,systypes t,#t tt
WHERE o.id=c.id
    AND c.xusertype=t.xusertype
    AND t.xtype=tt.xtype
    AND OBJECTPROPERTY(o.id,N'IsUserTable')=1

OPEN tb
FETCH tb INTO @sql,@sql1
WHILE @@FETCH_STATUS=0
BEGIN
    SET @sql1=N'IF EXISTS('+@sql+N') '+@sql1
    EXEC sp_executesql @sql1,N'@value sql_variant,@sql nvarchar(4000)',@value,@sql
    FETCH tb INTO @sql,@sql1
END
CLOSE tb
DEALLOCATE tb
SELECT * FROM #

exec sp_ValueSearch '要搜索的值',1 --1或不输入（即默认值1）精确匹配
exec sp_ValueSearch '要搜索的值',0 --不等于1，模糊匹配

---------------第二种方法------------------

Create   PROC xb_GetTableNameAndColNameForValue
 @value varchar(200)
AS
--求test库中包含值为@value的表和列名

--存储表名和列名
IF object_id('tabss') IS NOT NULL 
 exec('drop table tabss')
CREATE TABLE Tabss(id int identity(1,1),tabname varchar(100),colName varchar(100))

--查询某表某列是否包含某个值
IF object_id('ysgs') IS NOT NULL
 exec('drop proc ysgs')
exec('create PROC ysgs(@tab varchar(100),@col varchar(100))
AS
exec(''select 1 from ''+@tab+'' where ''+@col+'' like ''''%'+@value+'%'''''')')

--将结果存入tabss表中
EXEC master.dbo.xp_execresultset 'SELECT ''exec ysgs ''''''+object_name(id)+'''''',''''''+name+'''''';if @@rowcount>0  insert tabss (colname,tabname)values(''''''+name+'''''',''''''+object_name(id)+'''''')'' FROM syscolumns s WHERE xtype in(SELECT xtype FROM systypes s2 WHERE name in(''char'',''varchar'',''nchar'',''nvarchar''))
AND id in(SELECT id FROM sysobjects s2 WHERE xtype=''u'')',N'test'


GO


/*调用
exec xb_GetTableNameAndColNameForValue 'aa_1'
SELECT * FROM tabss
*/

------------------------第三种方法---------------------
declare @name nvarchar(100)
declare cur cursor for select name from sysobjects where type = 'U'
open cur
fetch next from cur into @name
WHILE @@FETCH_STATUS = 0
begin

    declare @sql nvarchar(500),@s varchar(500)
    set @s =''
    set @sql='select @s=isnull(@s+''+'','''')+'''''',''''''+''+cast(''+name+'' as varchar)'' from syscolumns where id=object_id('''+@name+''') and xtype in(175,239,99,231,35,167) ' 
    exec sp_executesql @sql,N'@s varchar(500) out',@s out
    if len(@s) > 0 
        exec ('if exists(select 1 from (select '+ @s+' as col from ['+@name+']) b where charindex(''aa'',col)>0) print '''+@name+'''')
    fetch next from cur into @name
end
close cur
DEALLOCATE cur

------第4种方法---------（来自小梁）

CREATE TABLE tb(id int,col varchar(20))
CREATE TABLE tb2(id int,data varchar(20))

INSERT tb VALUES(1,'中国');
INSERT tb VALUES(2,'liangck')

INSERT tb2 VALUES(1,'China')
GO

CREATE PROCEDURE dbo.FindString
    @string NVARCHAR(100)
AS

    DECLARE @SQL NVARCHAR(4000);
    SET @SQL = N'
            DECLARE @str NVARCHAR(4000);
            SELECT
                @str = ISNULL(@str + N'' OR '' + c.name + N'' LIKE N''''%' 
                                                + @string + ' %'''''',
                               c.name + N'' LIKE N''''%' + @string +'%'''''') FROM syscolumns AS c JOIN systypes AS t ON c.id=OBJECT_ID(''?'')
                AND c.xtype=t.xtype
                AND t.name IN(''varchar'',''char'',''nvarchar'',''nchar'');

            SET @str = ''SELECT TOP 1 1 FROM ? WHERE ''+@str;
            CREATE TABLE #tb(a int);
            INSERT #tb(a) EXEC(@str);
            IF EXISTS(SELECT * FROM #tb)
                PRINT ''?''
        ';
    EXEC sp_MsforeachTable @SQL;
GO

EXEC dbo.FindString N'中国'

GO
DROP PROCEDURE dbo.FindString
DROP TABLE tb,tb2




本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/09/20/4574197.aspx

[--小F--]

快起来 感冒了不好

[budong0000]

http://www.seekeep.com/html/computer/database/mssql/200802/627.html

[十一文]

[Pythony]

 


从数据库中查询所有表及所有字段的SQL语句
　　由于一个小项目的需要，近日完成一个从数据库中查询所有表及所有字段的方法，其实用两条SQL语句就可以完成。如果对你有用，记得回复一下哦
 
Sql Server版：

列出当前DB中所有表：
select name from dbo.sysobjects where xtype='u' and (not name LIKE 'dtproperties')

列出表中所有字段：
SELECT dbo.sysobjects.name as Table_name, dbo.syscolumns.name AS Column_name
FROM dbo.syscolumns INNER JOIN
      dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
WHERE dbo.sysobjects.name='TM_User'and (dbo.sysobjects.xtype = 'u') AND (NOT (dbo.sysobjects.name LIKE 'dtproperties'))
 
Oracle版：

列出当前DB中所有表：
select table_name from user_all_tables

列出表中所有字段：
SELECT column_name from user_tab_columns where table_name='EDL_TM_User')

[mashimaro_ra]

DECLARE tnames_cursor CURSOR
FOR
    SELECT o.name, c.name  FROM  syscolumns c inner join sysobjects o on c.id=o.id  and o.type='u' inner join systypes t on c.xtype=t.xtype and t.name='varchar' --改成你所需要的类型
    order by colid 
OPEN tnames_cursor
DECLARE @tablename sysname
DECLARE @cname sysname
DECLARE @num int
FETCH NEXT FROM tnames_cursor into @tablename,@cname
WHILE (@@FETCH_STATUS =0)
BEGIN
     EXEC('SELECT * FROM '+ @tablename+' where '+@cname+'=''1''')  --1替换成你要找的值    
        FETCH NEXT FROM tnames_cursor into @tablename,@cname
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

[「已注销」]

[Quote=引用 2 楼 fredrickhu 的回复:]

快起来 感冒了不好
[/Quote]
小F展示母爱了

[roverg]

好复杂，呵呵

[振乾]

------第4种方法---------（来自小梁）

CREATE TABLE tb(id int,col varchar(20))
CREATE TABLE tb2(id int,data varchar(20))

INSERT tb VALUES(1,'中国');
INSERT tb VALUES(2,'liangck')

INSERT tb2 VALUES(1,'China')
GO

CREATE PROCEDURE dbo.FindString
    @string NVARCHAR(100)
AS

    DECLARE @SQL NVARCHAR(4000);
    SET @SQL = N'
            DECLARE @str NVARCHAR(4000);
            SELECT
                @str = ISNULL(@str + N'' OR '' + c.name + N'' LIKE N''''%' 
                                                + @string + ' %'''''',
                               c.name + N'' LIKE N''''%' + @string +'%'''''') FROM syscolumns AS c JOIN systypes AS t ON c.id=OBJECT_ID(''?'')
                AND c.xtype=t.xtype
                AND t.name IN(''varchar'',''char'',''nvarchar'',''nchar'');

            SET @str = ''SELECT TOP 1 1 FROM ? WHERE ''+@str;
            CREATE TABLE #tb(a int);
            INSERT #tb(a) EXEC(@str);
            IF EXISTS(SELECT * FROM #tb)
                PRINT ''?''
        ';
    EXEC sp_MsforeachTable @SQL;
GO

EXEC dbo.FindString N'中国'

GO
DROP PROCEDURE dbo.FindString
DROP TABLE tb,tb2

支持这个，使用了 系统函数sp_MsforeachTable ，效率更高一些。

[jxjdzwang5555]

do while vartype(lnnumber) ='n'
accept "please input a number" to lnnumber
enddo

use table
copy stru to mytable
use mytable in 0 alias mytable
use table in 0 alias table
sele table
lnfield=fcount()
scan
scatt to amyfield(lnfield)
for i=1 to lnfield
if type('field(i)') !='n'
return
endif
if EVALUATE(field(i)) !=lnnumber
exit
endif
endfor
sele mytable
grat from amyfield
endscan

wait
use mytable
brow

[-晴天]

这样处理太麻烦,而且效率不高.
最好是等知道了那些表名和列名再做,专用的语句比通用的语句好得多.
难道还有什么比
declare @c nvarchar(10)
set @c='13'
select * from A where a=@c and b=@c and c=@c
能更好地运行么!

[猪小娣]

顶上顶上顶上顶上顶上

[djvfe]

看的眼真花

[luoyefeng1022]

Declare @value char(10)
Set @value='ddd'
--Select * from syscolumns Where id=object_id('tbdept') order by colid
Declare @s nvarchar(4000)
Select @s=isnull(@s,'Select * from tbDept Where ')+ QuoteName(Name)+'='+ QuoteName(ltrim(rtrim(@value)),'''')+' Or '
From syscolumns Where id=object_id('tbdept') order by colid
Select @s=left(@s,len(@s)-3)
exec (@s)
你要的，把表名换成你的表名就 OK！

[shunny_tech]

等值连接吗？