SQL SERVER – Find any expression in the database

My colleagues asked me hundred times, how to find a particular columns, table name, stored procedure, views in the database.

Always forgot to remember one query. So for them only šŸ™‚

Below query you can any table, stored procedure or views who ever have that expression as a column or object name.

SELECT * FROM SYSOBJECTS WHERE ID IN (SELECT ID FROM SYSCOMMENTS WHERE TEXT LIKE ‘%PRODUC%’)

The query produced all the object names. where ever it finds expression like ‘PRODUC

If you wanted to find any table name onlyĀ then

SELECT * FROM SYSOBJECTS WHERE NAME LIKE ‘%TA%’ and xtype=‘u’

result

For stored procedureĀ  passĀ  xtype=‘p’ and for views xtype=‘v’

Hope next time my friends will visit from my blog to get this query šŸ˜‰

Rana

Advertisements

SQL Server 2005 – How to shrink the truncated log file.

To shrink the Trucated Log file.

Following code always shrinks the Trucated Log File to minimum size possible.

USE Master
--You can replace with the database name but if it doesnt 
--works then replace with master databas)
GO DBCC SHRINKFILE(<TransactionLogName>, 1) 
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY 
DBCC SHRINKFILE(<TransactionLogName>, 1)