T-SQL


To do List for T-SQL:
  1. Databases, Schemas, Logins
  2. Tables, Data types,Compression, Data Integrity
  3. DDL: CREATE,alter,drop,truncate
  4. DML: Insert,update,delete, output, merge, select into
  5. Transactions -TCL :Commit,rollback,savepoint, Locking 
  6. DCL: Grant,revoke
  7. Bulk Insert, Bulk Copy and Export
  8. Stored procedures : Variables/Control structure, Exception handling
  9. Cursors
  10. Functions
  11. Triggers : Virtual tables/Magic tables
  12. Views, Synonyms
  13. Indexes
  14. XML Data
  15. Database Mail
  16. Challenges Faced and Solutions


Challenges Faced and Solutions:

Get all the tables that are referenced by a foreign key:

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
Where tab2.name='child table'

SQL Bulk Copy Class-Copy data between tables on different servers

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx


Error Tracking while using xp_cmdshell

-- This table will be used to gather the output of xp_cmdshell
create table XPCmdShellOutput (OutputLine varchar(1000))
-- Show the output of xp_cmdshell when the directory does not exist
Insert into XPCmdShellOutput
Execute master..xp_cmdshell 'bcp tempdb..Employee out c:\DirectoryDoesNotExist\Employee.txt -c'

Check if a Text File exists in SQL

declare @FileOK  INT

exec xp_fileExist 'c:\autoexec.bat' ,@FileOK OUTPUT

Print @FileOK


Read a Text File in SQL - 2 Ways to do this are below

SELECT * FROM OPENROWSET(BULK N'<PATH_TO_FILE>', SINGLE_CLOB) AS Contents
BULK INSERT dbo.temp 
   FROM 'c:\temp\file.txt'
   WITH 
      (
         ROWTERMINATOR ='\n'
      )


Function to extract numbers in a string

Create function GetNumbersFromString(@InputString nvarchar(200))

returns int

as

begin

Declare @Result int

if (PATINDEX('%[0-9]%',PARSENAME(Replace(@InputString,')',''),1))> 0) 
 set @Result = convert(int,SUBSTRING(PARSENAME(Replace(@InputString,')',''),1), PATINDEX('%[0-9]%',PARSENAME(Replace(@InputString,')',''),1)), LEN(Replace(@InputString,')',''))))
else 
 set @Result = 0
return @Result
end

Move Objects from one Schema to other

Alter Schema schemaname Transfer oldschemaname.ObjectName

Get Prime Numbers

declare @a int

declare @b int

declare @c float

declare @d int



select @a = 10

select @b = 10000

select @c = @a

select @d = 0



while @a < @b 
begin

select @c = (convert(int, sqrt(@a)) ) + 1

while @c > 1
begin


if (select (@a/@c) - (convert(int, @a/@c) )) = 0
  select @d=@d+1
  if (select (@a/@c) - (convert(int, @a/@c) )) = 0
  break
select @c=@c-1
end
if (select @d) = 0
  print convert(varchar(10),@a) + ' is a prime number'
select @d = 0
select @a=@a+1
end

Convert words to Proper case

CREATE FUNCTION [dbo].[ToProperCase](@string VARCHAR(255)) RETURNS VARCHAR(255)
AS
BEGIN
  DECLARE @i INT           -- index
  DECLARE @l INT           -- input length
  DECLARE @c NCHAR(1)      -- current char
  DECLARE @f INT           -- first letter flag (1/0)
  DECLARE @o VARCHAR(255)  -- output string
  DECLARE @w VARCHAR(10)   -- characters considered as white space

  SET @w = '[' + CHAR(13) + CHAR(10) + CHAR(9) + CHAR(160) + ' ' + ']'
  SET @i = 0
  SET @l = LEN(@string)
  SET @f = 1
  SET @o = ''

  WHILE @i <= @l
  BEGIN
    SET @c = SUBSTRING(@string, @i, 1)
    IF @f = 1 
    BEGIN
     SET @o = @o + @c
     SET @f = 0
    END
    ELSE
    BEGIN
     SET @o = @o + LOWER(@c)
    END

    IF @c LIKE @w SET @f = 1

    SET @i = @i + 1
  END

  RETURN @o
END

Split a CSV string row into columns

CREATE FUNCTION [dbo].[SplitIndex](@Delimiter varchar(20) = ' ', @Search varchar(max), @index int)
RETURNS varchar(max)
AS
BEGIN
 DECLARE @ix int,
 @pos int,
@rt varchar(max)

 DECLARE @tb TABLE (Val varchar(max), id int identity(1,1))

 SET @ix = 1
 SET @pos = 1


 WHILE @ix <= LEN(@search) + 1 BEGIN

SET @ix = CHARINDEX(@Delimiter, @Search, @ix)

IF @ix = 0
 SET @ix = LEN(@Search)
ELSE
 SET @ix = @ix - 1

INSERT INTO @tb
SELECT SUBSTRING(@Search, @pos, @ix - @pos + 1)

SET @ix = @ix + 2
SET @pos = @ix
 END

 SELECT @Rt = Val FROM @Tb WHERE id = @index
 RETURN @Rt     
END
Use like so:
SELECT dbo.SplitIndex(' ', 'hello World', 1)

Get result rows into a single string

Get result rows into a single string

select  @RoleName = COALESCE(@RoleName + ',', '')  + Cast(name as varchar) from rbl.roles  ORDER BY roleid

Pass CSV Values to Procedure

Create Function [dbo].[fnSplitter] (@IDs Varchar(100) )  
Returns @Tbl_IDs Table  (ID Int)  As  

Begin 
 -- Append comma

 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching

 Declare @Pos1 Int
 Declare @pos2 Int

 -- Start from first character 

 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@IDs)
 Begin
  Set @Pos1 = CharIndex(',',@IDs,@Pos1)
  Insert @Tbl_IDs Select  Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
  -- Go to next non comma character

  Set @Pos2=@Pos1+1
  -- Search from the next charcater

  Set @Pos1 = @Pos1+1
 End 
 Return
End

Calling the function
select  @RoleName = COALESCE(@RoleName + ',', '')  + Cast(name as varchar) from rbl.roles where roleid in (Select ID From fnSplitter(@RoleId))  ORDER BY roleid


Copy from excel to sql


INSERT INTO [dbo].[MyTable]  ( [Column1 ], [Column2] )
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\MyTable.xls',
'SELECT * FROM [Sheet1$]')

Delete all Stored Procedures and table:
right click on results pane and select result to text
select 'Drop table ' +
    s.name +'.'+p.name    
from
    sys.tables p
inner join
    sys.schemas s ON p.schema_id = s.schema_id
select 'Drop Procedure ' +
    s.name +'.'+p.name    
from
    sys.procedures p
inner join
    sys.schemas s ON p.schema_id = s.schema_id
Where p.type= 'P' and p.is_ms_shipped = 0 and p.name not like 'sp[_]%diagram%'

No comments:

Post a Comment

Subscribe to: Posts (Atom)