SQL Server 查询搜索命令、语句

rgbnj

--查询所有表
SELECT NAME,* FROM SYSOBJECTS WHERE XTYPE='U' order by SYSOBJECTS.name
--查询所有存储过程
select * from sysobjects where type='P' order by [name]
--查询表约束
exec sp_helpconstraint @objname=book_detail --@objname=prx_class
go
-- 查看内容(存储过程)
sp_helptext 'P_Sys_MenuList'




--通过某个字段名称找到数据库中对应的表
USE Works
GO
select DISTINCT object_name(id)
from syscolumns
where name like '%the_class%' order by 1
GO

------------------------------------------

select * from sysobjects
where object_name(id) in (
'OE_ORDER_LINES_ALL',
'OE_ORDER_HEADERS_ALL',
'OE_TRANSACTION_TYPES_TL',
'FNDLOOKUPVALUES',
'HZ_CUST_SITE_USES_ALL',
'HZ_CUST_ACCT_SITES_ALL')





/**全文检索（查询整个数据库中某个特定值所在的表和字段的方法 ）**/

一、


DECLARE @keyWord VARCHAR(200)
set @keyWord='hy'

-- 最大的长度.
DECLARE @MaxLength INT;
-- 数据库表名称
DECLARE @TableName VARCHAR(100);
-- 数据库表ID
DECLARE @object_id INT;
-- 查询列名的SQL
DECLARE @ColumnSql VARCHAR(500);
-- 列名.
DECLARE @clumnName VARCHAR(20);

-- 查询 数据是否存在的SQL
DECLARE @Sql NVARCHAR(500);

-- 查询 数据是否存在结果
DECLARE @RowCount INT;

BEGIN

-- 取得 传入的 字符串长度
-- 对于 表定义中， 字段长度小于 该长度的，将不检索.
SET @MaxLength = LEN(@keyWord);

-- 定义游标. 取得所有的数据库表名称.
DECLARE c_test_main CURSOR FAST_FORWARD FOR
    SELECT object_id, name FROM sys.tables

-- 打开游标.
OPEN c_test_main;

-- 填充数据.
FETCH NEXT FROM c_test_main INTO @object_id, @TableName;

-- 假如检索到了数据，才处理.
WHILE @@fetch_status = 0
BEGIN

    -- 准备用于执行的 查询列信息的 SQL 语句.
    SET @ColumnSql = 'DECLARE cur_column CURSOR for SELECT col.name '
      + ' FROM sys.columns col, sys.types typ '   -- 从列视图 与 数据类型视图查询
      + ' WHERE '
      + '   col.system_type_id = typ.system_type_id' -- 列视图 与 数据类型视图 关联
      + '   AND typ.name IN(''varchar'', ''nvarchar'', ''char'', ''nchar'') ' -- 只查询指定数据类型的.
      + '   AND col.max_length > ' + LTRIM(STR(@MaxLength )) -- 最大长度要大于 输入信息长度的
      + '   AND col.object_id =' + LTRIM(STR(@object_id)); -- 表ID

    EXEC(@ColumnSql);

    OPEN cur_column;
    FETCH next FROM cur_column INTO @clumnName;
    WHILE @@FETCH_STATUS=0
      BEGIN

        SET @Sql = 'SELECT @RowCount = COUNT(1) FROM ' + @TableName + ' WHERE ' + @clumnName + ' LIKE ''%' + @keyWord + '%'' ';

        EXEC SP_EXECUTESQL @Sql, N'@RowCount INT OUTPUT', @RowCount OUTPUT;

        IF @RowCount > 0
        BEGIN
          PRINT @TableName + '.' + @clumnName;
        END

        FETCH next FROM cur_column INTO @clumnName;
      END
    CLOSE cur_column;
    DEALLOCATE cur_column;

    -- 填充下一条数据.
    FETCH NEXT FROM c_test_main INTO @object_id, @TableName;
END;
-- 关闭游标
CLOSE c_test_main;
-- 释放游标.
DEALLOCATE c_test_main;
END



二、


declare @sql nvarchar(max),@search nvarchar(100)
set @search='hy'
set @sql=''
select @sql=@sql+'select top 1 '''+sysobjects.name+''' as [table],'''+syscolumns.name+''' as [columns],'+syscolumns.name+' as [value] from '+sysobjects.name+' where '+syscolumns.name+'='''+@search+''' union all ' from sysobjects inner join syscolumns on sysobjects.id=syscolumns.id where sysobjects.xtype='U' and syscolumns.xtype in (34,35,99,167,175,231,239,241,231)
select @sql=@sql+'select null,null,null'
exec sp_executesql @sql



三、


select convert(varchar(255),'') dsca
into #y
where 1=0

-- delete #y
declare @s varchar(255)
set @s='hy'
DECLARE bbb cursor for
select  TABLE_NAME,column_name  FROM information_schema.columns
    where table_name in (select  table_name from information_schema.tables where TABLE_type='BASE TABLE' and table_name like '%%' )
    and data_type like '%varchar%'


declare @t varchar(255)
declare @f varchar(255)
open bbb
fetch  next from bbb into @t,@f
while @@fetch_status=0
begin

exec( ' if exists (select * from '+@t+' where '+@f+'='+''''+@s+''''+'  )  insert into #y select '+''''+@t+'.'+@f+''''  )


--insert into #t select  '+''''+'select * from  '+@t+' WHERE '+@f+'=@F'+'''

fetch  next from bbb into @t,@f

end
close bbb
deallocate  bbb

select * from #y
drop table #y