SQLSkills blogspot: February 2014

How to delete/drop all the tables

Note: ---- For Delete all tales enable below

SET @statement = 'Delete TABLE ' + @Nombre;

--- For Drop all tales enable below

SET @statement = 'DROP TABLE ' + @Nombre;

use jiradb

DECLARE @Nombre NVARCHAR(MAX);

DECLARE curso CURSOR FAST_FORWARD

FOR

Select Object_name(object_id) AS Nombre from sys.objects where type = 'U'

OPEN curso

FETCH NEXT FROM curso INTO @Nombre

WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)

BEGIN

DECLARE @statement NVARCHAR(200);

---- For Delete all tales enable below

SET @statement = 'Delete TABLE ' + @Nombre;

--- For Drop all tales enable below
SET @statement = 'DROP TABLE ' + @Nombre;

print @statement

execute sp_executesql @statement;

END

FETCH NEXT FROM curso INTO @Nombre

END

CLOSE curso

DEALLOCATE curso

Remove all functions ,constrains, tables,Procedures fro Database

/* Drop all non-system stored procs */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null

BEGIN

SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'

EXEC (@SQL)

PRINT 'Dropped Procedure: ' + @name

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])

END

/* Drop all views */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL

BEGIN

SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'

EXEC (@SQL)

PRINT 'Dropped View: ' + @name

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])

END

/* Drop all functions */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL

BEGIN

SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'

EXEC (@SQL)

PRINT 'Dropped Function: ' + @name

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])

END

/* Drop all Foreign Key constraints */

DECLARE @name VARCHAR(128)

DECLARE @constraint VARCHAR(254)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null

BEGIN

SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

WHILE @constraint IS NOT NULL

BEGIN

SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint) +']'

EXEC (@SQL)

PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name

END

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

END

/* Drop all Primary Key constraints */

DECLARE @name VARCHAR(128)

DECLARE @constraint VARCHAR(254)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL

BEGIN

SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

WHILE @constraint is not null

BEGIN

SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT [' + RTRIM(@constraint)+']'

EXEC (@SQL)

PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name

END

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

END

/* Drop all tables */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL

BEGIN

SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'

EXEC (@SQL)

PRINT 'Dropped Table: ' + @name

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])

END

Truncate All Tables - Part I

This procedure will truncate all the tables in the database

To truncate all tables,I have seen the programmers using Cursors to get all tables and truncate them.This will avoid Cursors. Running this procedure will make SQL Server to slow down other tasks. So test this with test server and make sure that you are not running any other SQL Server related processes





Create Procedure Truncate_All_Tables
as

Declare @t table(query varchar(1000),tables varchar(50))

Insert into @t 

    select 'Truncate table ['+T.table_name+']', T.Table_Name from 

    INFORMATION_SCHEMA.TABLES T

    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

    on T.table_name=TC.table_name 

    where (TC.constraint_Type ='Foreign Key' or TC.constraint_Type is NULL) and 

    T.table_name not in ('dtproperties','sysconstraints','syssegments') and     Table_type='BASE TABLE' 

Insert into @t 

    select 'delete table ['+T.table_name+']', T.Table_Name from  INFORMATION_SCHEMA.TABLES T

    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC

    on T.table_name=TC.table_name where TC.constraint_Type ='Primary Key' and 

    T.table_name <>'dtproperties'and Table_type='BASE TABLE' 

Declare @sql varchar(8000)

Select @sql=IsNull(@sql+' ','')+ query from @t

Exec(@sql)

GO

Run this procedure as

EXEC Truncate_All_Tables

Truncate All Tables - Part II


statements. This is other method



Set NoCount ON




Declare @tableName varchar(200)

set @tableName=''



While exists



( 



--Find all child tables and those which have no relations




    select T.table_name from INFORMATION_SCHEMA.TABLES T



    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC



    on T.table_name=TC.table_name 


    where (TC.constraint_Type ='Foreign Key'or TC.constraint_Type is NULL) and



    T.table_name not in ('dtproperties','sysconstraints','syssegments')and 


    Table_type='BASE TABLE' and T.table_name > @TableName)




Begin




    Select @tableName=min(T.table_name) from INFORMATION_SCHEMA.TABLES T



    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC



    on T.table_name=TC.table_name 


    where (TC.constraint_Type ='Foreign Key'or TC.constraint_Type is NULL) and 


    T.table_name not in ('dtproperties','sysconstraints','syssegments') and 


    Table_type='BASE TABLE' and T.table_name > @TableName

    --Truncate the table



    Exec('Truncate table '+@tablename)



End



set @TableName=''




While exists



( 


--Find all Parent tables 




    select T.table_name from INFORMATION_SCHEMA.TABLES T



    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC



    on T.table_name=TC.table_name 


    where TC.constraint_Type ='Primary Key'and T.table_name <>'dtproperties' and 


    Table_type='BASE TABLE' and T.table_name > @TableName )




Begin




    Select @tableName=min(T.table_name) from INFORMATION_SCHEMA.TABLES T



    left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on T.table_name=TC.table_name 


    where TC.constraint_Type ='Primary Key' and T.table_name <>'dtproperties' and 


    Table_type='BASE TABLE' and T.table_name > @TableName 






    --Delete the table



    Exec('Delete from '+ @tableName) 






    --Reset identity column 


    If exists


    ( 


        select * from information_schema.columns 


        where COLUMNPROPERTY(OBJECT_ID(QUOTENAME(table_schema)+'.'+


        QUOTENAME(@tableName)), column_name,'IsIdentity')=1


    ) 


    DBCC CHECKIDENT (@tableName, RESEED, 1)




End



Set NoCount Off

Undocumented DATE and TIME related functions

It is quite surprising to know the undocumented date and time related functions that work in both sql server 2000 and 2005

Run these and see(I think they are self-explanatory Smile

)

select {fn current_date()}
select {fn current_time()}
select {fn now()}
select {fn extract(hour from getdate())}
select {fn extract(minute from getdate())}
select {fn extract(second from getdate())}
select {fn extract(day from getdate())}
select {fn extract(month from getdate())}
select {fn extract(year from getdate())}
select {fn dayname(GetDate())}
select {fn monthname(GetDate())}
select {fn month(GetDate())}
select {fn year(GetDate())}

Handle ISDATE() with care

Now-a-days it becomes common practice for newbies to use varchar datatype to store dates. My first advice is to use proper DATETIME datatype. Sometimes it is preferred to use varchar when importing dates from other systems(to avoid any errors if the source dates are in different format). In that case it is required to see if values are actually dates. To check if a value is proper date, it is suggested to use ISDATE()

select isdate('2007-10-12'),isdate('12-12-2006'),isdate('March 12, 2007')

Now it should be noted that the following return 1

select isdate(2007),isdate('2007')

becuase the values in a ISDATE functions are first casted to datetime

select cast(2007 as datetime)
------------------------------------------------------
1905-07-01 00:00:00.000

select cast('2007' as datetime)
------------------------------------------------------
2007-01-01 00:00:00.000

Which are valid dates

So if varchar datatype is used to stored formatted dates (ex YYYYMMDD), to check if it is valid date, you need not only to use ISDATE() function, but also use LEN() function

Consider the following set of data and see the difference

declare @dates table (dates varchar(8))

insert into @dates(dates)Select '20071201' union allSelect '20071111' union allSelect '2007' union allSelect 2007 union allSelect '1800'

select dates from @dateswhere ISDATE(dates) =1

--Result
dates
--------
20071201
20071111
2007
2007
1800

--Result

select dates from @dateswhere ISDATE(dates) =1 and LEN(dates)=8

dates

--------

20071201

20071111

SELECT DATENAME(year, '2014/04/28');
Result: '2014'

SELECT DATENAME(yyyy, '2014/04/28');
Result: '2014'

SELECT DATENAME(yy, '2014/04/28');
Result: '2014'

SELECT DATENAME(month, '2014/04/28');
Result: 'April'

SELECT DATENAME(day, '2014/04/28');
Result: '28'

SELECT DATENAME(quarter, '2014/04/28');
Result: '2'

SELECT DATENAME(hour, '2014/04/28 09:49');
Result: '9'

SELECT DATENAME(minute, '2014/04/28 09:49');
Result: '49'

SELECT DATENAME(second, '2014/04/28 09:49:12');
Result: '12'

SELECT DATENAME(millisecond, '2014/04/28 09:49:12.726');
Result: '726'

Remove duplicate characters from a string

Here is its alternate method with Number table approach



create procedure remove_duplicate_characters(@string varchar(100))


as


Declare @result varchar(100)



set @result=''




select @result=@result+min(substring(@string ,number,1)) from 


( 


    select number from master..spt_values where type='p' and number between 1 and len(@string )

) as t 

group by substring(@string,number,1)

order by min(number)


select @result 
GO


EXEC remove_duplicate_characters 'aasssarrrty'



returns



asrty

Count number of words in a string

Here are two more methods


1 Using the method I posted already about Squeeze Function to remove multiple spaces



Declare @s varchar(100)


set @s=' See       how many                        words this      has '



Select len(string)-len(replace(string,' ',''))+1 as no_of_words from

(


    Select replace(replace(replace(ltrim(@s),' ',' ~!@#'),'~!@# ',''),'~!@#','') as string 


) as t 





2 Using Number table approach used to Extract only numbers from a String




Declare @s varchar(100),@result varchar(100)



set @s=' See       how many                        words this      has '
set @result=''


Select @s=replace(replace(replace(ltrim(@s),' ',' ~!@#'),'~!@# ',''),'~!@#','') 




select count(*)+1 as no_of_words from


( 


    select substring(@s,number,1) as number from 


    (  


        select number from master..spt_values where type='p' and number between 1 and len(@s) 


    ) as t

) as t 

where number='' 





Extract only numbers from a String

Sometimes we may need to extract only numbers from a string. Usually I see people using while loop to check each and every character to see if it is a number and extract it

Here is a different approach

Declare @s varchar(100),@result varchar(100)set @s='as4khd0939sdf78'

set @result=''

select @result=@result+case when number like '[0-9]' then number else '' end from
( select substring(@s,number,1) as number from ( select number from master..spt_values where type='p' and number between 1 and len(@s) ) as t ) as t select @result as only_numbers

Result




Squeeze Function

The Squeeze function is used to remove the multiple occurences of spaces into one occurence. In SQL Server there is no function to do the same. I needed to write this in my application to remove unwanted spaces in the string.

Run the following and see the result


declare @t table(string_col varchar(100))




insert into @t


select 'a              b   c' union all 


select 'a                   b               c' union all 


select 'a       b                                        c' union all 


select 'a             b          c' union all 


select 'a   b    c' 


select 


         string_col, 


         replace(replace(replace(string_col,' ',' ~!#$^'),'~!#$^ ',''),'~!#$^','') as new_string_col 


from 

         @t



If you use front end application (VB,.NET,jave,etc), you can also simulate the same approach there

Posted by Madhivanan with 15 comment(s) 
Filed under: sql server, squeeze





Column length and Data length

When you use character datatypes(char,varchar,nchar,etc),
always make sure that column has enough length to have data. 

Otherwise sometimes you would get errors and sometimes you wont. 



Consider the following example

Declare @t table(i int, test_col varchar(2))

Insert into @t select 1,'test'
Select i,test_col from @t



Well. You get the error 
Msg 8152, Level 16, State 14, Line 4

String or binary data would be truncated.
The statement has been terminated.

Because the column length of test_col is not enough to have the value 'test'


But only when you insert data to a table, you get this error. 
In other cases you dont
Declare @v varchar(2)

set @v='test'
select @v

Now there is no error but the value is truncated and 

only first two characters are assigned to the variable. 



The same case is applied when you use a stored procedure with input parameter create procedure test(@test_param varchar(2))
as


Select @test_param

Go
EXEC test1 'test'

Go
drop procedure test

Now see what happens when you dont specify the length


Declare @v varcharset @v='test'
select @v

By default the length is 1

Consider another example

Select cast('This has more than thirty characters' as varchar)
The result is This has more than thirty char




When you cast a literal to character type without specifying the length, 

by default it is 30. 




So you need to specify the enough column length when you use character datatypes

SQLSkills blogspot

Search This Blog

GUNNU

How to delete/drop all the tables

Remove all functions ,constrains, tables,Procedures fro Database

Truncate All Tables - Part I

Truncate All Tables - Part II

Undocumented DATE and TIME related functions

Handle ISDATE() with care

Remove duplicate characters from a string

Count number of words in a string

Extract only numbers from a String

Squeeze Function

Column length and Data length

Translate

My Blog List