sql server²éѯÊý¾Ý¿âÖаüº¬Ö¸¶¨×Ö·û´®µÄÊý¾Ý±í
·¢²¼Ò»¸öʵÓÃС¹¤¾ß£¬¿ÉÒԺܷ½±ãµÄÔÚÊý¾Ý¿âÖÐÕÒµ½°üº¬Ö¸¶¨×Ö·û´®µÄÊý¾Ý±íÃû¼°ÏàÓ¦¼Ç¼£º
/*
¹¦ÄÜ£º²éѯÊý¾Ý¿âÖаüº¬Ö¸¶¨×Ö·û´®µÄÊý¾Ý±íÃû¼°ÏàÓ¦¼Ç¼
×÷Õߣº³Â¼ÓÅô chjpeng#163.com
ÈÕÆÚ£º2009-08-17
*/
declare @key varchar(30)
set @key = 'test' --Ìæ»»ÎªÒª²éÕÒµÄ×Ö·û´®
DECLARE @tabName VARCHAR(40),@colName VARCHAR(40)
DECLARE @sql VARCHAR(2000)
declare @tsql varchar(8000)
DECLARE tabCursor CURSOR FOR
SELECT name from sysobjects WHERE xtype = 'u' AND name <> 'dtproperties'
OPEN tabCursor
FETCH NEXT from tabCursor INTO @tabName
WHILE @@fetch_status = 0
BEGIN
set @tsql = ''
DECLARE colCursor CURSOR FOR Select Name from SysColumns Where id=Object_Id(@tabName) and xtype=167
OPEN colCursor
FETCH NEXT from colCursor INTO @colName
WHILE @@fetch_status = 0
BEGIN
SET @sql = 'if(exists(select * from ' + @tabName + ' where '
SET @sql = @sql + @colName + ' like ''%' + @key + '%'')) begin select * from '
set @sql = @sql + @tabName + ' where ' + @colName + ' like ''%' + @key + '%'';select '''
+ @tabName + ''' as TableName end'
set @tsql = @tsql + @sql + ';'
FETCH NEXT from colCursor INTO @colName
END
exec(@tsql)
CLOSE colCursor
DEALLOCATE colCursor
FETCH NEXT from tabCursor INTO @tabName
END
CLOSE tabCursor
DEALLOCATE tabCursor
Ïà¹ØÎĵµ£º
Êý¾Ý²ã´ÎµÄ¸ÅÄî:
Êý¾Ý²ã´ÎÊDZí´ïÊý¾ÝµÄÒ»ÖÖÖØÒª¹ØÏµ£¬ÔÚÊý¾Ý¿âµÄÉè¼ÆÖУ¬È磺×éÖ¯½á¹¹·Ö½â¡¢¹¤×÷ÈÎÎñ·Ö½â¡¢ÐÐÕþÇø»®µÄ·Ö½âµÈ¶¼ÊDzã´Î¹ØÏµÊý¾ÝµÄµäÐÍʵÀý¡£
±í´ï²ã´Î¹ØÏµµÄÊý¾ÝÒ»°ãÐèҪʵÏÖÈçÏÂÊôÐÔ£º
1.²ã´ÎµÄ×î´ó¼¶Áª²ã´ÎÊý¡£È磺Öйú£>ºþÄÏÊ¡->³¤É³ÊÐ->Óê»¨Çø£¬¾ÍÊÇ4²ã¡£
2.ÄÜ·´Ó³Í¬Ò»² ......
USE master
GO
CREATE DATABASE testbase2
ON
PRIMARY
(NAME = prim_sub_dat1,
FILENAME = 'G:\SQL SERVER2000\prim_sub_dat.mdf',
SIZE = 5MB,
MAXSIZE = 50MB,
FILEGROWTH = 20%),
(NAME = prim_sub_dat2,
FILENAME = 'G:\SQL SERVER2000\prim_sub_dat.ndf'
SIZE = 5MB,
MAXSIZE = 50MB,
......
Sample1:
/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
/* Build and Execute a Transact-SQL String with a single parameter value Using EXECUTE Command */
SET @EmpID = 1001
SET @SQLQuery = 'SELECT * from tblEmployees WHERE EmployeeID = ' + CAST(@EmpID A ......
ÓÃSQLÓï¾äÌí¼Óɾ³ýÐÞ¸Ä×Ö¶Î
1.Ôö¼Ó×Ö¶Î
alter table docdsp add dspcode char(200)
alter table tbl add meet_group int2
2.ɾ³ý×Ö¶Î
ALTER TABLE table_NAME DROP COLUMN column_NAME
3.ÐÞ¸Ä×Ö¶ÎÀàÐÍ
&nbs ......
Óë³Ö¾Ã±íÒ»Ñù£¬ÓÅ»¯Æ÷´´½¨²¢Î¬»¤ÁÙʱ±íµÄ·Ö²¼Í³¼ÆÐÅÏ¢£¬²¢¸ú×ÙËüµÄ»ùÊý¡£µ±Ë÷ÒýÁÙʱ±íʱ£¬ÕâÖÖÄÜÁ¦ÓÈÆäÖØÒª¡£µ±ÓÅ»¯Æ÷ÐèÒªÆÀ¹ÀÑ¡ÔñÐÔʱ£¬Ëü¾Í¿ÉÒÔ¸ù¾ÝÕâЩ·Ö²¼Í³¼ÆÐÅÏ¢Éú³É¾¹ýÓÅ»¯µÄ¼Æ»®¡£ÕâÊÇÁÙʱ±íÔÚÐÔÄÜ·½Ã治ͬÓÚ±í±äÁ¿µÄÖ÷ÒªÌØÐÔÖ®Ò»¡£
´ËÍ⣬ÒòΪÁÙʱ±í»áά»¤Í³¼ÆÐÅÏ¢£¬Èç¹ûÉϴαàÒëºó±»ÒýÓñíÓÐ×ã¹»¶àµÄÐз¢ ......