Archive | SQL RSS feed for this section

SQL Query for geting all Stored Procedure under a schema

10 Dec

 SELECT *
FROM DB_Name.information_schema.routines
WHERE routine_type = ‘PROCEDURE
AND SPECIFIC_SCHEMA=’SchemaName

Using this query we can get all the stored procedure under  SchemaName in DB_Name

Advertisements

FOR GETTING MONTHS BETWEEN TWO DATES IN SQL

20 Jan

For getting month names between two dates we may use the following Query

DECLARE @date1 DATETIME,@date2 DATETIME

SET @date1 = ‘2010-12-20’

SET @date2 = GETDATE();

WITH cte

AS (

SELECTdatename(month, @date1) AS [Month_Name], @date1 AS dat

UNIONALL

SELECTdatename(month, DateAdd(Month, 1, dat)), DateAdd(Month, 1, dat) FROM cte

WHEREDateAdd(Month, 1, dat) < @date2

)

SELECT [Month_Name]

FROM CTE

 

OURTPUT

Query for Geting dependent tables of a stored procedure

17 Dec

In MS SQL Server 2008, you may get the table names dependents by a stored procedure using the following query

SELECT name

,(SELECT name FROM sys.schemas WHERE schema_id=t.schema_id) AS schemaName

FROM  sys.tables t

WHERE  t.object_id in (SELECT depid

FROM sys.sysdepends

WHERE id In (SELECT s.object_id FROM sys.procedures s WHERE name=’SPName‘))

SPName  is the name of  stored procedure.

Th output will be the table name and its schema. Happy querying .. 🙂

Query for getting SP associated with a table

4 May

Here is the query for getting all stored procedure name associated with a particular table in SQL server 2008.

EXEC sp_depends tableName

This query gives all the stored procedures associated with the table ‘tableName’

Query for getting Table structure in MS SQL Server

25 Feb

Here is the query for getting the structure , properties and values of a table in MS SQL

exec sp_help tablename

Where ‘tablename’ is the name of table.

RESETING IDENTITY COLUMN IN SQL SERVER

12 Oct

DBCC CHECKIDENT(‘tableName’, RESEED, 0)

Nexe record will be started with identity 1

CREATING USER DEFINED TABLE TYPE IN SQL SERVER 2008

5 Oct

USE [DB]
GO
CREATE TYPE [dbo].[typename] AS TABLE(
[column1] [type](size) NULL/NOT NULL,
[column2] [type](size) NULL/NOT NULL,
[column3] [type](size) NULL/NOT NULL,

..

..

)

GO