最近自己接触MSSQL的机会比较多,有工作的过程中发现会经常用到一些查询,对于小型数据库来说可能用不上,但对于一个数据库有几百甚至上千张表的时候,你就会发现这可不是一般的实用。
实例一:MSSQL查询某一字段在哪一个表中存在:
select a.[name] from sysobjects a,
(
select [id],count(*) b from syscolumns
where [name] =’column1‘
group by [id]
)
b where a.[id]=b.[id] order by a.name asc
实例二:MSSQL查询某两个不同字段在哪一个表中存在:
select a.[name] from sysobjects a
left join
(
select [id],count(*) b from syscolumns where [name]
in(‘column1‘,’column2‘) group by [id] having count(*)>1
) b
on a.[id]=b.[id]
where b.id is not null
备注:以上红色部分为您要修改的部分,闵涛整理时间2015.6.23 22:32
实例三:查询整个数据库中某个特定值所在的表和字段的方法
CREATE PROCEDURE [dbo].[SP_FindValueInDB]
(
@value VARCHAR(1024)
)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql VARCHAR(1024)
DECLARE @table VARCHAR(64)
DECLARE @column VARCHAR(64)
CREATE TABLE #t (
tablename VARCHAR(64),
columnname VARCHAR(64)
)
DECLARE TABLES CURSOR
FOR
SELECT o.name, c.name
FROM syscolumns c
INNER JOIN sysobjects o ON c.id = o.id
WHERE o.type = ‘U’ AND c.xtype IN (167, 175, 231, 239)
ORDER BY o.name, c.name
OPEN TABLES
FETCH NEXT FROM TABLES
INTO @table, @column
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘IF EXISTS(SELECT NULL FROM [‘ + @table + ‘] ‘
SET @sql = @sql + ‘WHERE RTRIM(LTRIM([‘ + @column + ‘])) LIKE ”%’ + @value + ‘%”) ‘
SET @sql = @sql + ‘INSERT INTO #t VALUES (”’ + @table + ”’, ”’
SET @sql = @sql + @column + ”’)’
EXEC(@sql)
FETCH NEXT FROM TABLES
INTO @table, @column
END
CLOSE TABLES
DEALLOCATE TABLES
SELECT *
FROM #t
DROP TABLE #t
End
实例三使用方法:
例如,要查询‘admin’,新建一个查询输入
EXEC SP_FindValueInDB ‘admin‘
会返回相应记录,Tablename显示被查询数据所在表,Columnname显示被查询数据所在列。
0 条评论。