Category: Database

pivot sql server

SELECTSELECT R.UserID, R.RoleIDFROM User_Role R;
DECLARE @cols AS NVARCHAR(MAX),    @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT DISTINCT ‘,’ + QUOTENAME(RoleID)                     from User_Role                    group by UserID,RoleID            FOR XML PATH(”), TYPE            ).value(‘.’, ‘NVARCHAR(MAX)’)         ,1,1,”)
set @query = ‘SELECT ‘ + @cols + ‘ from              (                select UserID,RoleID                from User_Role            ) x            pivot             (                UserID                for RoleID in (‘ + @cols + ‘)            ) p ‘
execute(@query);
select * from yourtable

Advertisements

Get all trigger in sql server

SELECT
sysobjects.name AS trigger_name
,USER_NAME(sysobjects.uid) AS trigger_owner
,s.name AS table_schema
,OBJECT_NAME(parent_obj) AS table_name
,OBJECTPROPERTY( id, ‘ExecIsUpdateTrigger’) AS isupdate
,OBJECTPROPERTY( id, ‘ExecIsDeleteTrigger’) AS isdelete
,OBJECTPROPERTY( id, ‘ExecIsInsertTrigger’) AS isinsert
,OBJECTPROPERTY( id, ‘ExecIsAfterTrigger’) AS isafter
,OBJECTPROPERTY( id, ‘ExecIsInsteadOfTrigger’) AS isinsteadof
,OBJECTPROPERTY(id, ‘ExecIsTriggerDisabled’) AS [disabled]
FROM sysobjects

INNER JOIN sysusers
ON sysobjects.uid = sysusers.uid

INNER JOIN sys.tables t
ON sysobjects.parent_obj = t.object_id

INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id

WHERE sysobjects.type = ‘TR’

Create user and enable remote Mysql

1) log-in your mysql via terminal as “root” –> mysql -uroot -p
2) CREATE USER ‘gioinv’@’%’ IDENTIFIED BY ‘your-password’ ;

3)

mysql> GRANT ALL ON database.* TO user@'10.10.0.2' IDENTIFIED BY 'user-password-here';
mysql> FLUSH PRIVILEGES;
(IP can be replaced with % to allow this user to logon from any host or IP.)

If connection still fails, check “bind-address” option in /etc/my.cnf file.

bind-address option is IP address to bind to.

4: /etc/my.cnf file

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
bind-address=192.168.33.10
port = 3306
# skip-networking

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid