Gettin Table Name using SP name
June 17, 2009 at 4:14 pm | In SQL | Leave a CommentHi all its really fun working on queries.. I have been updating my blog with queries as i learn them.. Today i was in need of a query to find the tables from the sp name. Atlast i got it. Here it is… The first one list the tables and stored procedures linked withthe sp name we give. The later one lists the table name alone.
Query 1:
select name from sysobjects
where id in (select sd.depid from sysobjects so, sysdepends sd
where so.name = ‘InsertWorkflowCompany’ and
sd.id = so.id )
Query 2:
select name from sysobjects
where id in (select sd.depid from sysobjects so, sysdepends sd
where so.name = ‘InsertWorkflowCompany’ and
sd.id = so.id) and type =’U‘
In the second query type = ‘p’ can be used to list the stored procedures linked.
Happy Querying !!
Cheers !!
To Get SP Details
May 22, 2009 at 7:30 pm | In SQL | Leave a CommentHi
These are the 2 queries to get the Sored Procedure details related to a Particular Database and Particular Table.
use db name
Select * From sys.sysobjects where type=’p’
SELECT * from sys.sysobjects so
inner join sys.syscomments sc
on so.id=sc.id
where type=’p’ and sc.text like ‘%TableName%’
where p stands for the sproc.
Inserting Multiple Rows using single SQL statement !!!
February 25, 2008 at 9:12 pm | In SQL | 5 CommentsTags: Single Insert Statement, SQL Insert Query
I was surprised when i came to know that there is single SQL statement available to insert more than one row at a time. So i decided to write a post on this , inorder to share it with others.
How we generally do :
INSERT INTO TableName(Id,Name)
VALUES (1,'Name1')
INSERT INTO TableName(Id,Name)
VALUES (2,'Name2')
The other way is
INSERT INTO TableName(Id,Name)
SELECT 1,'Name1'
UNION ALL
SELECT 2,'Name2'
UNION ALL
SELECT 3,'Name3'
Cool!!!! Isnt It?????
sql reseed
December 21, 2007 at 5:04 pm | In SQL | 3 CommentsI used to get irritated with my identity column when it increases to somewhere in 300s in my sample applications. I used to drop my table and create it again.Today i saw an article for resetting the identity column.
use databasename
go
dbcc checkident(tablename, reseed,seed value)
This will search the table for the highest ID field, and reset the seed to the seed value specified.
While we are at it, here are some other useful DBCC commands:
To check the integrity of a table:
dbcc checktable(tablename)
To force a table to rebuild its indexes:
dbcc dbreindex(tablename)
SQL Prompt
December 19, 2007 at 4:25 pm | In SQL | Leave a CommentTags: Red Gate, Sql Prompt
Red Gate is a software that provides intellisense in SQL. The !4 day trial of this software can be downloaded here.
A snap shot of it is shown below.
Blog at WordPress.com. | Theme: Pool by Borja Fernandez.
Entries and comments feeds.
