SQL Types In SYSOBJECTS

The query:
 
select 

distinct type from sysobjects
FN: Scalar Valued Functions
P: Stored Procedures
U: Tables
TF Table Valued Functions
How to delete all of the same type?
use
mydb –replace by your own db name
GO
declare
@procName sysnamedeclare

someCursor cursor FORselect

name from sysobjects where type = ‘P’ AND objectproperty(id, ‘IsMSShipped’ = 0 order by name ascopen

someCursor
fetch
next from someCursor into @procName
while
[...]

Read Full Post »

Loop Through A Comma Delimited String In SQL

DECLARE @Items VARCHAR(1000)
SET @Items = ‘A,B,CD,E,FGH,KL,MNOP,QRSTU,V,W,XYZ’
DECLARE @Item VARCHAR(50)
DECLARE @Pos INT
DECLARE @Loop BIT
SELECT @Loop = CASE WHEN LEN(@Items) > 0 THEN 1 ELSE 0 END
WHILE (SELECT @Loop) = 1
BEGIN
SELECT @Pos = CHARINDEX(’,’, @Items, 1)
IF @Pos > 0
BEGIN
SELECT @Item = SUBSTRING(@Items, 1, @Pos - 1)
SELECT @Items = SUBSTRING(@Items, @Pos + 1, LEN(@Items) - @Pos)
END
ELSE
BEGIN
SELECT @Item = @Items
SELECT [...]

Read Full Post »

Grant Execute Permission To All Stored Procedures

The script:
USE SomeDB

DECLARE @name varchar(128), @sql varchar(500)

DECLARE procs CURSOR FAST_FORWARD FOR

SELECT name FROM sysobjects WHERE type = ‘U’ ORDER BY name ASC FOR READ ONLY

OPEN procs

FETCH next FROM procs INTO @name

WHILE [...]

Read Full Post »

Case Statement In SQL

SELECT SomeColumn1 =
(
  SELECT
    CASE
      WHEN SomeColumn3 IS NULL THEN ISNULL(SomeColumn3,”       ELSE ISNULL(SomeColumn4,”     END
  FROM SomeTable2 WHERE SomeColumn5 = @SomeParameter2
)
FROM SomeTable1
WHERE SomeColumn2 = @SomeParameter1

Read Full Post »

Insert Statement And Return Identity

INSERT SomeTable
(
  @SomeColumn1,
  @SomeColumn2
)
VALUES
(
  @SomeParameter1,
  @SomeParameter2
)
SELECT IDENT_CURRENT(‘SomeTable’

Read Full Post »

Creating A Temp Table In SQL

CREATE TABLE #TempTable
( 
  SomeColumn1 INT,
  SomeColumn2 VARCHAR(50)
)
INSERT INTO #TempTable
SELECT SomeColumn3, SomeColumn4 FROM SomeTable WHERE SomeColumn5 = @SomeParameter
DROP TABLE #TempTable

Read Full Post »

Set A Variable In A Stored Procedure From A Select Statement

DECLARE @Value
INSERT @Value = (SELECT SomeColumn1 FROM SomeTable WHERE SomeColumn2 = @SomeParameter)

Read Full Post »