Feeds:
Posts
Comments

Posts Tagged ‘column’


-- New Table Structure 
CREATE TABLE dbo.FirstTable(
	FirstField varchar(100) null
) 


--Table Rename -- 
exec sp_rename 'dbo.FirstTable', 'dbo.TableRenamed'

-- Columns Rename 
exec sp_rename  'dbo.FirstTable.FirstField', 'FieldRenamed'


/* as a note, instead of using sp_rename to rename COLUMNS an alternative method is to use the ALTER table command to change a column's data type or (to re-name it)a new column can be added and old one dropped */

alter table dbo.TableRenamed 
add FieldRenamedSecondTime varchar(100) NULL

alter table dbo.TableRenamed 
drop column FieldRenamed varchar(100) NULL

-- To just change the datatype:
alter table dbo.TableRenamed 
alter column FieldRenamed int NULL

Read Full Post »

The different options available to eliminate/handle decimals

declare @value as float = 123.9876
select ceiling(@value), floor(@value) -- eliminate decimals 
select round(@value,2)

Find triggers inside a database (Courtesy to Harry Lee)

select st.name, strig.name, st.name
from sys.triggers strig inner join sys.tables st
	on strig.parent_id = st.object_id inner join sys.schemas sch
	on st.schema_id = sch.schema_id

Format number as currency

SELECT CONVERT(varchar, CAST(987654321 AS money), 1) 

Find column names in a given table

SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.name = 'dbo.Foo' --you can change text to other dataTypes
ORDER BY 1--,c.OBJECT_ID;

Note on – pivoting accuracy with numbers
————————————
I have seen from experience that when Pivoting its better to use float or decimal (if you results can contain fractional values) and not integers to get more accurate results. If integer is required then the numbers need to be converted after the Pivotting.

Check IF Table is empty
—————————

To chekc if a record or a certain record exists

if exists(select top 1 * from TableName) — use the where clause to check for a certain record

The if exists checks whether any rows were returned. If I use top 1 in the selected statement that supposedly sohuld be more optimized because it does not need to show the showl table.
The star is used so that it can just be copied and reused by just changing the table name.

Generate Scripts (Backup scripts)
———————————–
Right click on a database
> Task
> Generate Scripts

Select the type of objects
Click the Advances button to see what how you want the script to be produced.

This way you would have all or your selected object include tables and its data all scripted out in a file.

Read Full Post »

Below are three samples (from the same script) of how columns can be dynamically selected.

Simple :


/*
Dynamic column selection 

In Thing x number of columns need to be displayed and it change each time – a solution could be:
If the names of columns can be captured in a table then the below code could be used to build a dynamic sql:

store all the fields names in the variable:

*/

SELECT  @pivotedfields= COALESCE(@pivotedfields + ',[' + category + ']','[' + category + ']')


-- Sample:

set @pivotedfields = 'Select ' 
	SELECT  @pivotedfields= COALESCE(@pivotedfields + ',[' + category + ']','[' + category + ']')
						+ '')
	FROM    TableContainingColumnNames
	set @pivotedfields = @pivotedfields + ' from tableName'




-- Example of how to format any one type of column (taken from project_reports_cba)


			set @strSQL3 = ' select [Month],  dbo.udf_FormatToCurrency ([' + @Drill  
				+ '], ''%'', 2, ''false'', ''false'') [' +  @Drill
				+ '] from #tmpFinalnew order by closed_month' -- UMA (3/2/2012) Added square brackets 
			execute (@strSQL3 )

Complex:

	
	-------------- ================== UMA 17/4/2012  - Making columns dynamic ====================== ---------------- 
	 
	    -- select top 10 * from #tmpSavings_res1 -- test `  -- common table containing the fields 
	    
	    create table #distinctFields -- the table needed to be created becuase SQL doesn't allow select inot the same table in a if else 
	    (
			FieldName varchar(50) 
	    )
	    	    
	    --- Extract the distinct field names 
	    if @Bar <> 'Motor' -- for all and casualty 
	    begin 
			insert into #distinctFields
			select distinct Bar 
			from #tmpSavings_res1
		end 
		else 
		begin 
			insert into #distinctFields
			select distinct Drill  
			from #tmpSavings_res1
		end 
	    
	    --select * from #distinctFields -- test ` 
	    
		if (select count(1) from #distinctFields) = 2 -- When just one new/product type is selected 
		begin											-- delete the total row 
			delete from #distinctFields
			where FieldName = 'Total'
		end 

		DECLARE @listedFields NVARCHAR(2000) -- = ''
		
		-- get a pivot list 
		SELECT  -- @listedFields= @listedFields  + ', [' + isnull(FieldName, '')  + ']'
				@listedFields = COALESCE(@listedFields + ',[' + FieldName + ']',
										'[' + FieldName + ']') -- COALESCE helps to stat the list without. this line is first execute and for the rest the above is. 
		FROM    #distinctFields 
		ORDER BY FieldName 
		
		 select @listedFields -- test ` -- List of fields that are requested to be seen 

		declare @MySQL varchar(max) -- This is where I will hold all my dynamic SQL

	-------------- ======================================== ---------------- 		

Extensive:

-------------- ================== Making columns dynamic ====================== ---------------- 
	 
	    
	    create table #distinctFields -- the table needed to be created becuase SQL doesn't allow select inot the same table in a if else 
	    (
			FieldName varchar(50) 
	    )
	    	    
	    --- Extract the distinct field names 
	    if @Bar <> 'Apple' 
	    begin 
			insert into #distinctFields
			select distinct Bar 
			from #tmpSavings_res1
		end 
		else 
		begin 
			insert into #distinctFields
			select distinct Drill  
			from #tmpSavings_res1
		end 
	    
	    --select * from #distinctFields -- test ` 
	    
		if (select count(1) from #distinctFields) = 2 -- When just one new/product type is selected 
		begin											-- delete the total row 
			delete from #distinctFields
			where FieldName = 'Total'
		end 

		DECLARE @listedFields NVARCHAR(2000) -- = ''
		
		-- get a pivot list 
		SELECT  -- @listedFields= @listedFields  + ', [' + isnull(FieldName, '')  + ']'
				@listedFields = COALESCE(@listedFields + ',[' + FieldName + ']',
										'[' + FieldName + ']') -- COALESCE helps to stat the list without. this line is first execute and for the rest the above is. 
		FROM    #distinctFields 
		ORDER BY FieldName 
		
		 --select @listedFields -- test ` -- List of fields that are requested to be seen 

		declare @MySQL varchar(max) -- This is where I will hold all my dynamic SQL

	-------------- ======================================== ---------------- 		

						declare @totalPT float 
						set @totalPT = (select sum(value) from #tmpSavings_res1	 where Bar = 'Total')
						
						INSERT INTO #tmpSavings_res1 (Bar, closed_month_txt, value, closed_month) -- no Drills
						SELECT b.Bar	 [Fruit],
								'Percentage' [Month],
								(select (sum(a.value)/@totalPT) * 100 
									from #tmpSavings_res1 a	
									where a.Bar = b.Bar
									group by a.Bar 
								), 
								getdate() 
						FROM   #tmpSavings_res1 b 
									--where category <> 'Total'  -- Need to show the Total for this one 
						GROUP BY b.Bar	
						
						--select * from #tmpSavings_res1 -- test ` 
							
							-- insert into #tmpFinal excluding the above 
						set @MySQL = 
							'select [Month], ' + 
								@listedFields +
							' into ##tmpFinal_Revamped
							from 
							(		    
								select	Bar [Fruit],
										closed_month_txt [Month], 
										cast(round(sum([value]),0) as int) [Value],
										closed_month
								from #tmpSavings_res1
								group by	Bar ,
											closed_month,
											closed_month_txt 

										
							)	as sourcetable
							PIVOT
							( 
							sum([Value])
							FOR [Fruit] in (' + 
								@listedFields + 
								 ')
							) as Final' 
							
							--print @MySQL -- test `
							exec (@MySQL) 
							
							select * from ##tmpFinal_Revamped -- test ` 


Read Full Post »

-- Option one 

	--------========= Making Columns Dynamic so that if if clients ask for more products later on they should automatically come up ------ 
	declare @columnList varchar(max) 
		,@MySQL varchar(max)
		
	SELECT @columnList= COALESCE(@columnList + ',[' + Place + ']'
							,'[' + Place + ']')
	--FROM @SortPlace
	FROM (select distinct Place from @FetchData where Place <> 'Average'
			union 
			select 'Average' -- this will be appened at the end
			) dl 
	
	select @columnList -- test ` 
	--------==========================================

--- OPtion two 

--------========= Making Columns Dynamic so that if if clients ask for more products later on they should automatically come up ------ 
	declare @columnList varchar(max) 
		,@MySQL varchar(max) 
		
	declare @SortPlace table 
	(
		Serial int identity,
		Place varchar(500)   
	)
	insert into @SortPlace
	select distinct  Place from  @FetchData where Place <> 'Average'
	insert into @SortPlace values ('Average') --- this is done so that it appears in the bottom
	 
	
	 
	SELECT @columnList= COALESCE(@columnList + ',[' + Place + ']'
							,'[' + Place + ']')
	FROM @SortPlace
	
	select @columnList -- test ` 
	--------==========================================
	
	
--- OPtion three (Recommended) 



declare @Field2Issue table 
(	
	Field2_Types varchar(800),
	Weighted int
)
insert into @Field2Issue
select distinct Field2_Types,
	Thing Field2_Types 
		when 'First Type2' then 1
		when 'Second Type2' then 2

		--when 'Total' then 9
	end Weighted 
from @SelectedData
order by Weighted

--select * from @Field2Issue -- Test ` 

--------========= Making Columns Dynamic  ------ 
declare @columnList varchar(max), @SumColumnList varchar(max) 
	,@MySQL varchar(max) 
 
SELECT  @columnList= COALESCE(@columnList + ',[' + Field2_Types + ']'
						,'[' + Field2_Types + ']') 
						
FROM (select distinct Weighted, Field2_Types  from @Field2Issue) dl -- included MonthEnd for the right sort order 

--select @columnList  -- test ` 
--------==========================================
 
 
 -- In the above the field "Weighted" sorts the entries So any columns with numbers as the first field will sort this entry in ascending order. 
 
 
 
 
 --- Option four 
 
 /*
	The fields itself can have the numbers prefixed in asecnding order for e.g. 
	4 jelly, 2 flower, 3 fruit, 1 egg
	
	later on when the final pivotting is done remove the number and any space after that and it should allign properly as below:

	egg, flower, fruit, jelly 

*/	
 
 
 

Read Full Post »

--Check if a table exists (and drop it if does) before creating it again
IF EXISTS (
  SELECT NAME
  FROM anydbnameOptional.dbo.sysobjects -- use TYPE to specify tables, Sps etc
  WHERE type = 'u' -- <a href="http://msdn.microsoft.com/en-us/library/ms177596.aspx">http://msdn.microsoft.com/en-us/library/ms177596.aspx</a>
   AND NAME = 'whatever'
  )
 DROP TABLE whatever

--Check if a database exists
IF EXISTS (
  SELECT NAME
  FROM master..sysdatabases
  WHERE NAME = 'DatabaseName'
  )
 SELECT 'ok'

DECLARE @anotherWay VARCHAR(100)

-- if select is used then the outer bracket must be used as well
--set @anotherWay = (select DB_NAME())
SET @anotherWay = DB_NAME()

PRINT @anotherWay

--=====================
-- To check and delete an object
--if object_id ('dbo.udf_CapitaAnsweredCallsKPI8') is not null
--    drop function dbo.udf_CapitaAnsweredCallsKPI8
--go
----=========================
-------- =========== Check if a column exists  ================================ --------------------------
IF EXISTS (
  SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'myTableName'
   AND COLUMN_NAME = 'myColumnName'
  )
 ---- The below way (sys.columns) is depricated
 IF EXISTS (
   SELECT *
   FROM sys.columns
   WHERE NAME = N'columnName'
    AND Object_ID = Object_ID(N'tableName')
   )
 BEGIN
  -- Column Exists
 END
   ----------------------====================================================----------------------------------

Read Full Post »

Source: http://www.bigresource.com/Tracker/Track-ms_sql-p72iL0Yw/

Hi,

I’ve a text column (text datatype) that contains carriage return and line feed.

Syntax-wise, how can I replace these by a space?

Thanks.

————-

Hi,

I just got the answer. Thanks.

Source:
http://www.winnetmag.com/SQLServer/Article/ArticleID/20699/20699.html

SELECT

	TextId,

	replace ( replace(TextValue, char(10),

	''), char(13), '') ModifiedTextValue

FROM

	BadTextData

Read Full Post »