Code By Experience

In this tutorial i will check  ' TestCulumns'  column exists in a table, when its not exist, add this column. IF EXISTS ( SELECT * FROM sys.columns WHERE Name = N 'TestCulumns' AND Object_ID = Object_ID(N 'Categories' )) BEGIN SELECT * FROM Categories; END ELSE BEGIN ALTER TABLE Categories ADD TestCulumns VARCHAR ( 20 ); END

Get current year : SELECT TO_CHAR(SYSDATE, 'YYYY' ) AS Current_Year FROM DUAL; First Day of Current Year: SELECT TO_CHAR(TRUNC (SYSDATE , 'YEAR' ) , 'DD/MM/YYYY' ) FROM DUAL; Last Day of Current Year: SELECT TO_CHAR(ADD_MONTHS(TRUNC (SYSDATE , 'YEAR' ), 12 )- 1 , 'DD/MM/YYYY' ) AS LastDay_Current_Year FROM DUAL; First Day of Previous Year: SELECT TO_CHAR(ADD_MONTHS (TRUNC (SYSDATE, 'YEAR' ), - 12 ), 'DD/MM/YYYY' ) AS FirstDay_ Previous_Year FROM DUAL;  Last Day of Previous Year: SELECT TO_CHAR(ADD_MONTHS (TRUNC (SYSDATE, 'YEAR' ), - 1 ) + 30 , 'DD/MM/YYYY' ) AS LastDay_PreviousYear FROM DUAL;

When I try to run DBCC repair I get the below the errors .Database needs to DBCC CHECKTABLE( 'Person.Address' , REPAIR_REBUILD ) Msg 7919, Level 16, State 3, Line 1 Repair statement not processed. Database needs to be in single user mode. The correct way to run a DBCC CHECKDB or DBCC CHECKTABLE statement with valid REPAIR options is to start SQL Server normally and then explicitly set the database in single user mode. You can do this from either the Enterprise Manager or the Query Analyzer. When single user mode set to true only one user can connect to the server. From Enterprise Manager: Right-click the database name, and then click  Properties . In the  Properties  dialog box, click  Options . Select the  single user  option, and then click  OK .        From Query Analyzer: SP_DBOPTION 'AdventureWorks' , SINGLE, TRUE Run DBCC command CHECKDB: DBCC CHECKTABLE( 'Pers...

Checks the integrity of all the pages and structures that make up the table or indexed view. Syntax: DBCC CHECKTABLE ( table_name | view_name [ , { NOINDEX | index_id } |, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] ) [ WITH { ALL_ERRORMSGS ] [ , EXTENDED_LOGICAL_CHECKS ] [ , NO_INFOMSGS ] [ , TABLOCK ] [ , ESTIMATEONLY ] [ , { PHYSICAL_ONLY | DATA_PURITY } ] [ , MAXDOP = number_of_processors ] } ] DBCC CHECKTABLE( 'Production.Product' )

Database Consistency Checker act as Database Console Commands give details in form of statistics about the SQL Server. They can be used for Maintenance of database, index, or file group. DBCC Commands can be used to perform validation operations on a database, table, index, catalog, file group, or allocation of database pages. Database Console Command statements are grouped into the following categories. Maintenance   :  Maintenance tasks on a database, index, or filegroup. Miscellaneous  :  Miscellaneous tasks such as enabling trace flags or removing a DLL from memory. Informational  :  Tasks that gather and display various types of information. Validation         :  Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages. DBCC Internal Database Snapshot command  DBCC CHECKALLOC DBCC CHECKDB DBCC CHECKCATALOG DBCC CHECKFILEGROUP DB...

  In this tutorial, I will create a utility function used for string split and return in a table. ALTER FUNCTION Splitstring_to_table ( @string NVARCHAR( MAX ), @ delimiter CHAR ( 1 ) ) RETURNS @ output TABLE ( id INT IDENTITY ( 1 , 1 ) PRIMARY KEY , data NVARCHAR( MAX ) ) BEGIN DECLARE @ start INT , @ end INT SELECT @ start = 1 , @ end = CHARINDEX(@ delimiter , @string) WHILE @ start < LEN(@string) + 1 BEGIN IF @ end = 0 SET @ end = LEN(@string) + 1 INSERT INTO @ output ( data ) VALUES ( SUBSTRING (@string, @ start , @ end - @ start )) SET @ start = @ end + 1 SET @ end = CHARINDEX(@ delimiter , @string, @ start ) END RETURN END push string to function, see result SELECT * FROM dbo.Splitstring_to_table( 'Bangladesh, America, German,' , ',' ) Charindex : In SQL Server (Transact-SQL), the CHARINDEX function...

I have done this with Sql server temp table. SELECT * INTO #temp FROM CIF_CARDpro_IRIS_USDDATA --Select from temp table SELECT * FROM #temp --List of columns SELECT COUNT (name) NumOfColumns FROM tempdb.sys.columns WHERE object_id = object_id( 'tempdb..#temp' ); --drop temp table DROP TABLE #temp