Query to find a Table inside a Stored Procedure

Find a Stored Procedure Related to Table in Database - Search in All Stored Procedure.

Sometimes we mite need to find a table used inside a Stored Procedure

we can check for a particular table being used or not before deleting or Modifying the Structure or Changing a Primary Key etc.

—-Option 1
SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%tablename%'

—-Option 2
SELECT DISTINCT o.name, o.xtype FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE c.TEXT LIKE '%tablename%'

Query to find a column name inside a Database

Query to find a column name inside a Database

SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%EmployeeID%’
ORDER BY schema_name, table_name;

Query to change Collation of a Database

I faced a probem with a join on 2 database.
After digging for a while i found that one of my database
COLLATION was SQL_Latin1_General_CP1_CI_AS and Second was Latin1_General_CI_AI
Collation Related Error are based on Sql Server Setup or a database Restore from some external server.
As you know database can directly be restored in Sql Server 2005 without "Create new Database" and then "Restore"
Sql Server 2005
option 1. 'Latin1_General_CI_AI' (Fresh Setup on OS having no Instance of SQL Server)
option 2. 'SQL_Latin1_General_CP1_CI_AS' (Compatibility with previous versions of Sql Server.)

Query to Check for Collation
--> First Select your Database and then try this Query
PRINT 'MY DATABASE [' + DB_NAME() + '] COLLATION IS: ' + CAST( DATABASEPROPERTYEX ( DB_NAME(), N'COLLATION' ) AS VARCHAR(128) ) PRINT 'MY TEMPDB DATABASE COLLATION IS: ' + CAST( DATABASEPROPERTYEX ( 'TEMPDB', N'COLLATION' ) AS VARCHAR(128) )

Query to Change collation of A Database
ALTER DATABASE yourDBName COLLATE Latin1_General_CI_AI or SQL_Latin1_General_CP1_CI_AS or the one you need

Query all the Databases for Collation & compatibility level
SELECT NAME, COLLATION_NAME, COMPATIBILITY_LEVEL, * FROM SYS.DATABASES
SELECT SERVERPROPERTY('COLLATION')