Useful Sql Queries

,

Useful Sql Server Statements

1. Search through Column Name: - Suppose you want to know how many objects have the same column name. You can use this sql statement.

For Sql Server 2000:-

Select * from sysobjects where id in (Select * from Syscolumn where name='ColumnName')

For Sql Server 2005:-

select * from sys.objects where object_id in

(select object_id from sys.columns where name='ColumnName')

2. Search through Query statement: - Suppose you have a query and you want to know if this query is used in which views or which procedure. You can use this sql statement.

Select * from syscomments where name like ('%your Sql Query%')

3. Search constraint used in whole database: -Suppose you want to view all the constraint used in your database. You can use this sql statement.

select pr.name,fr.parent_object_id,fr.name,fr.type_desc from sys.objects pr,

sys.objects Fr where pr.type<>'s'

and pr.object_id=fr.parent_object_id

order by fr.parent_object_id

--Search through Column Name: - 
--Suppose you want to know how many objects have the same column name. 
--You can use this sql statement.
---For Sql Server 2000:-
Select * from sysobjects where id in 
(Select * from Syscolumn where name=’ColumnName’)
---For Sql Server 2005:-
select * from sys.objects where object_id in
(select object_id from sys.columns where name=’ColumnName’)
--Search through Query statement: - Suppose you have a query and you want to know if this query is used in which views or which procedure. You can use this sql statement.
Select * from syscomments where name like (‘%your Sql Query%’)
---Search constraint used in whole database: Suppose you want to view all the constraint used in your database. You can use this sql statement.
select pr.name,fr.parent_object_id,fr.name,fr.type_desc from sys.objects pr,
sys.objects Fr where pr.type<>'s'
and pr.object_id=fr.parent_object_id
order by fr.parent_object_id

Rate

2 (9)

Share

Share

Rate

2 (9)