Archive for the ‘Database’ Category
MSSQL Change Table Column name
February 22nd, 2012 | 
Author: to change a column name in MSSQL, run this query:
SQL Table Ownership Change
To change sql table owner from a name to dbo, use this sql:
SELECT @crtOwner= 'currentOwner_ToChange', @newOwner = 'dbo'
, @SQL = '
IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @crtOwner+ '''
)
EXECUTE sp_changeobjectowner ''?'', ''' + @newOwner + ''''
EXECUTE sp_MSforeachtable @SQL
For stored procs, execute this stored proc to get the sqls required for changing the owner.
Then copy the resulted sqls and run them in a new query.
SELECT @crtOwner= 'currentOwner_ToChange', @newOwner = 'dbo'
SELECT 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+
'.'+QUOTENAME(a.ROUTINE_NAME)+
''','''+@newOwner+''''
FROM
INFORMATION_SCHEMA.ROUTINES a
WHERE
a.ROUTINE_TYPE = 'PROCEDURE'
AND a.SPECIFIC_SCHEMA = @crtOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0
for views:
SELECT
@crtOwner = 'currentOwner_ToChange', @newOwner = 'dbo'
SELECT 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.TABLE_SCHEMA)+'.'+QUOTENAME(a.TABLE_NAME)+''','''+@newOwner+''''
FROM
INFORMATION_SCHEMA.VIEWS a
WHERE
a.TABLE_SCHEMA = @crtOwner
AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.TABLE_SCHEMA)+'.'+QUOTENAME(a.TABLE_NAME)), 'IsMSShipped') = 0
MSSQL function to return the date part from a datetime
in MSSQL, to return only the date part from a datetime ,
use this function:
AS
BEGIN
DECLARE @Today AS DATETIME
SET @Today = DATEDIFF(dd, 0, @dt)
RETURN @Today
END
An alternative approach:
This provides quick support to convert the date in any format. The table which suggest the date formats are displayed on MSDN:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
Some claims that using CONVERT is slower than using DATE functions, i made some tests and find out that it is approximately the same thing.
For a loop with 2 million iterations, the elapsed time for first approach was: 8186 ms, for the second approach was: 8253 ms.
MSSQL – select all parents details from a hierarchical tale
Having 2 tables, table 1 named ‘categories’ with the fields : ‘category_id’, ‘parent_id’, ‘category_name’
and table 2 named category_xref, with the fields ‘ category_id’,'parent_id’ (which is the references for the categories),
to select detail for all the parents for a given category, use this code:
(
SELECT parent_id AS category_id FROM category_xref WHERE category_id = @category_id
UNION
SELECT @category_id AS category_id
) t1 JOIN categories c ON c.category_id = t1.category_id
MSSQL – Update a table column incremental
SET @Id = 0
UPDATE TABLE_NAME SET @Id = column_name = @Id + 1
MYSQL – Selecting and updating a table column incremental
To select a column incrementally, starting from a certain value (in this case 0):
To update a column incrementally (from 0 to X)
SET column_name = (SELECT @rownum:=@rownum+1 AS newid FROM (SELECT @rownum:=0) r )
If you want to keep the initial order for the upper update:
SET column_name = (SELECT @rownum:=@rownum+1 AS newid FROM (SELECT @rownum:=0) r )
ORDER BY t.column_name
Sql Server 2005 Create Full Index Catalog Smo Job error
If you get this error when you try to define a full text index:
Could not load type ‘Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection’ from assembly ‘Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91′. (SqlManagerUI)
Go check this link:
http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/12181324-bc51-41a5-a8a5-608bfe2725e9
or …. read this:
“A couple things I noticed .. (1) this appears to be a non-issue if you allow the index at the time you create the database and (2) although one cannot seem to create a full-text index from the table I do believe I have a work-around.
Go to the Storage directory underneith the Tables directory. Expand to Full Text Catalogs. Right-click and create a New Full-Text Catalog. From there you should be able to link it to the Tables/Views that you wish to index. Problem with this is that you still cannot access the Population Schedule which generates the same error mentioned above if you try (JobBaseCollection PublicKeyToken=89845dcd8080cc91). Without a scheduled rebuild of the index you will likely need to rebuild it manually from time to time. Not so good for dynamic data but if your data is fairly static this should be ok.”
Bulk insert and optimization for mysql
To insert more items in a single query, use the following code:
(F1_value2,F2_value2),(F1_value3,F2_value3),(F1_value4,F2_value4)
For optimization, before the insert query, run this one: ALTER TABLE TableName DISABLE KEYS
and after insert, run this query: ALTER TABLE TableName ENABLE KEYS
You can exclude the primary key if it is autoincrement, as well you can specify it if you want your own values.
ALTER TABLE TableName DISABLE KEYS tells MySQL to stop updating non-unique indexes.
ALTER TABLE TableName ENABLE KEYS
MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup.
Using ALTER TABLE TableName DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.
Multiple Insert + MSSQL optimization
To insert more values in a single query:
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5
More details can be found here: http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/
————————————————————————————————————
To insert values from one table to another :
(
@PROGRAM_ID INT,
@LEAD_TYPE INT = NULL
)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO DST_USRTPL_MAXLEAD(USER_ID, LEAD_TYPE, MAX_LEADS, PER_UNIT, UNIT_ID, DAY_MAX, MONTH_MAX)
SELECT USER_ID, @LEAD_TYPE, MAX_LEADS, PER_UNIT, UNIT_ID, DAY_MAX, MONTH_MAX FROM DST_PROGRAM_MAXLEAD
WHERE PROGRAM_ID = @PROGRAM_ID AND USER_ID IS NOT NULL
SET NOCOUNT OFF
END
————————————————————————————————————
To insert more values sent as a string, separate by comma (i.e.: ’13,14,24,55′):
(
@PROGRAM_ID INT,
@USER_ID INT = NULL,
@GROUP_ID INT = NULL,
@LOCATION_IDs VARCHAR(MAX) = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @LOCATION_IDs IS NOT NULL BEGIN
INSERT INTO DST_LOCATION_XREF(PROGRAM_ID, USER_ID, GROUP_ID, LOCATION_ID, IS_EXCLUDED)
SELECT @PROGRAM_ID, @USER_ID, @GROUP_ID, CONVERT(INT, [VALUE]), 0 FROM dbo.my_fn_SplitString(@LOCATION_IDs, ',')
END
where function my_fn_SplitString is :
@SourceString VARCHAR( MAX ) ,
@Delimiter VARCHAR( 5 ) = ','
)
RETURNS @VALUES TABLE( POSITION SMALLINT IDENTITY NOT NULL, VALUE VARCHAR( MAX ) NOT NULL )
AS
BEGIN
IF LEN( @SourceString ) > 0
BEGIN
DECLARE @START SMALLINT, @END SMALLINT
SET @Delimiter = COALESCE( @Delimiter, ',' )
SET @START = len( @Delimiter )
IF LEFT( @SourceString, len( @Delimiter ) ) <> @Delimiter
SET @SourceString = @Delimiter + @SourceString
IF RIGHT( @SourceString, len( @Delimiter ) ) <> @Delimiter
SET @SourceString = @SourceString + @Delimiter
WHILE( 1 = 1 )
BEGIN
SET @END = charindex( @Delimiter, @SourceString, @START + 1 )
IF @END = 0 BREAK
INSERT INTO @VALUES( VALUE ) VALUES( SUBSTRING( @SourceString, @START + 1, @END - @START - 1 ) )
SET @SourceString = stuff( @SourceString, 1, @END - 1, '' )
END
END
RETURN
END
Hierarchical structure for tables with parent-child relations
Many times, in a table with parent-child relationships, we need the entire hierarchical line of a record.
In this case, to set a hierarchical structure for a new table, see the following example:
in a table named USR_GROUP, with the fields GROUP_ID, PARENT_ID, TITLE, STATUS
add two more fields, GROUP_DEPTH (int), LINEAGE (varchar),
then execute the following queries, which will set the new fields. LINEAGE will be like: /1/4/7/9
WHERE (PARENT_ID = 0)
————————————
UPDATE T SET
T.GROUP_DEPTH = P.GROUP_DEPTH + 1,
T.LINEAGE = P.LINEAGE + Ltrim(Str(T.Parent_ID,6,0)) + '/'
FROM USR_GROUP AS T
INNER JOIN USR_GROUP AS P ON (T.Parent_ID=P.GROUP_ID)
WHERE (P.GROUP_DEPTH>=0) AND (P.LINEAGE IS NOT NULL) AND (T.GROUP_DEPTH IS NULL)
——————————