DECLARE @tableName VARCHAR(64); | |
SET @tableName = 'MY_TABLE_NAME'; | |
SELECT | |
SO_P.name AS parent_table | |
, SC_P.name AS parent_column | |
, 'is a foreign key of' AS direction | |
, SO_R.name AS referenced_table | |
, SC_R.name AS referenced_column | |
, * | |
FROM | |
sys.foreign_key_columns FKC | |
INNER JOIN sys.objects SO_P ON SO_P.object_id = FKC.parent_object_id | |
INNER JOIN sys.columns SC_P ON (SC_P.object_id = FKC.parent_object_id) AND (SC_P.column_id = FKC.parent_column_id) | |
INNER JOIN sys.objects SO_R ON SO_R.object_id = FKC.referenced_object_id | |
INNER JOIN sys.columns SC_R ON (SC_R.object_id = FKC.referenced_object_id) AND (SC_R.column_id = FKC.referenced_column_id) | |
WHERE | |
((SO_P.name = @tableName) AND (SO_P.type = 'U')) | |
OR | |
((UPPER(SO_R.name) = UPPER(@tableName)) AND (SO_R.type = 'U')) | |
; |
Category Archives: Tips
SQL Server script to scan the whole database looking for a specific value in a specific column
DECLARE @search_column VARCHAR(255); | |
DECLARE @search_value VARCHAR(255); | |
DECLARE @search_type INT; /* 1: specific column 2: string columns 3: numeric columns */ | |
SET @search_column = 'SEARCHING_COLUMN_NAME'; —wrap with %% for wider search | |
SET @search_value = 'Any value'; —wrap with %% for wider search | |
SET @search_type = 1; | |
DECLARE @containing_tables TABLE | |
( | |
table_name VARCHAR(255) NOT NULL, | |
column_name VARCHAR(255) NOT NULL, | |
column_value VARCHAR(255) NOT NULL | |
); | |
DECLARE all_tables_cursor CURSOR FAST_FORWARD for | |
SELECT | |
t.name AS 'TableName', c.name AS 'ColumnName' | |
FROM | |
sys.columns c JOIN sys.tables t ON c.object_id = t.object_id | |
WHERE | |
((@search_type = 1) AND (UPPER(c.name) LIKE UPPER(@search_column))) /* search into a specific column */ | |
OR | |
((@search_type = 2) AND (c.user_type_id IN (35, 99, 167, 175, 231, 239))) /* search into all string columns */ | |
OR | |
((@search_type = 3) AND (c.user_type_id IN (48,52,56,108,127))) /* search into all numeric columns */ | |
ORDER BY TableName, ColumnName; | |
DECLARE @table_name VARCHAR(255), @column_name VARCHAR(255) | |
OPEN all_tables_cursor | |
FETCH NEXT FROM all_tables_cursor INTO @table_name, @column_name | |
WHILE (@@FETCH_STATUS <> –1) | |
BEGIN | |
IF (@@FETCH_STATUS <> –2) | |
BEGIN | |
BEGIN TRY | |
DECLARE @select_statement VARCHAR(1000); | |
SET @select_statement = 'DECLARE all_values_cursor CURSOR FAST_FORWARD FOR SELECT [' + @column_name + '] FROM [' + @table_name + '] WHERE (CONVERT(VARCHAR(255), [' + @column_name + ']) LIKE ' + CHAR(39) + @search_value + CHAR(39) + ');'; | |
EXEC(@select_statement) | |
DECLARE @column_value VARCHAR(255) | |
OPEN all_values_cursor | |
FETCH NEXT FROM all_values_cursor INTO @column_value | |
WHILE (@@FETCH_STATUS <> –1) | |
BEGIN | |
IF (@@FETCH_STATUS <> –2) | |
BEGIN | |
INSERT INTO @containing_tables (table_name, column_name, column_value) VALUES (@table_name, @column_name, @column_value); | |
END | |
FETCH NEXT FROM all_values_cursor INTO @column_value | |
END | |
CLOSE all_values_cursor; | |
DEALLOCATE all_values_cursor; | |
END TRY | |
BEGIN CATCH | |
PRINT 'Error reading ' + @table_name + '.' + @column_name + ': ' + ERROR_MESSAGE(); | |
END CATCH; | |
END | |
FETCH NEXT FROM all_tables_cursor INTO @table_name, @column_name | |
END | |
CLOSE all_tables_cursor; | |
DEALLOCATE all_tables_cursor; | |
—Bonus instruction, you can customize the print below to accomodate specific needs | |
DECLARE all_selects_cursor CURSOR FAST_FORWARD for | |
SELECT | |
table_name, column_name, column_value | |
FROM | |
@containing_tables | |
GROUP BY table_name, column_name, column_value; | |
OPEN all_selects_cursor | |
FETCH NEXT FROM all_selects_cursor INTO @table_name, @column_name, @column_value | |
WHILE (@@FETCH_STATUS <> –1) | |
BEGIN | |
IF (@@FETCH_STATUS <> –2) | |
BEGIN | |
BEGIN TRY | |
PRINT 'SELECT * FROM ' + @table_name + ' WHERE (' + @column_name + ' = ' + CHAR(39) + @search_value + CHAR(39) + ');'; | |
END TRY | |
BEGIN CATCH | |
PRINT 'Error reading ' + @table_name + '.' + @column_name + ': ' + ERROR_MESSAGE(); | |
END CATCH; | |
END | |
FETCH NEXT FROM all_selects_cursor INTO @table_name, @column_name, @column_value | |
END | |
CLOSE all_selects_cursor; | |
DEALLOCATE all_selects_cursor; | |
SELECT table_name, column_name, column_value, count(column_value) AS repeating FROM @containing_tables GROUP BY table_name, column_name, column_value; |
Importing a public SSL certificate into a JVM
You are trying to connect your pretty application to an external HTTPS endpoint and getting an odd out of blue exception like below:
PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
That’s happening because, when connecting via HTTPS, the public SSL certificate from the destiny must be available to JVM truststore. Sometimes it is not updated. So, let’s do it with two simple command lines.
1. Have the public certificate in your machine:
Linux
openssl s_client -connect <HOST>:443 -servername <HOST>:443 < /dev/null | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > public.crt
Windows
openssl s_client -connect <HOST>:443 -servername <HOST>:443 < NUL | sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > public.crt
2. Import the public certificate:
<JDK_HOME>\bin\keytool -import -alias <HOST> -keystore <JRE_HOME>\lib\security\cacerts -file public.crt
All good!
SQL Server script to replicate all data from a remote database to a local database
/* | |
Replicates all data from the remote database to the local database. The local contents are removed, all current local data is erased and not recoverable. | |
You have to set the value for @LINKED_SERVER_NAME as your local Linked Server name for the remote SQL Server instance. | |
*/ | |
print 'Starting replication…'; | |
—Control for structure alterations over the process | |
declare @CONTROL_TABLES table | |
( | |
TABLE_NAME varchar(255) NOT NULL, | |
CONSTRAINT_NAME varchar(255) NULL, | |
STATUS_ENABLED int NULL, | |
HAS_IDENTITY int NULL | |
); | |
—Local linked server alias | |
declare @LINKED_SERVER_NAME varchar(255); | |
—Database name (both "from" and "to" must be same name) | |
declare @DATABASE_COPY varchar(255); | |
/** | |
Define controllers | |
**/ | |
print 'Setting controllers…'; | |
set @LINKED_SERVER_NAME = '127.0.0.1,11433'; | |
—Using local database name | |
select @DATABASE_COPY = db_name(); | |
—Identify all user tables with constraints | |
insert into @CONTROL_TABLES (TABLE_NAME, CONSTRAINT_NAME, STATUS_ENABLED, HAS_IDENTITY) | |
select | |
O2.NAME, | |
O.NAME, | |
case when ((C.STATUS & 0x4000)) = 0 then 1 else 0 end, | |
null | |
from SYS.SYSCONSTRAINTS C | |
inner join SYS.SYSOBJECTS O on O.ID = C.CONSTID | |
inner join SYS.SYSOBJECTS O2 on O2.ID = O.PARENT_OBJ | |
where | |
(O2.NAME in (select TABLE_NAME from INFORMATION_SCHEMA.TABLES where (TABLE_TYPE = 'BASE TABLE'))) | |
and (O.XTYPE in ('C', 'F')) | |
; | |
—Identify all user tables with identity | |
update A | |
set | |
HAS_IDENTITY = 1 | |
from | |
@CONTROL_TABLES as A | |
inner join (select | |
T.NAME as NAME | |
from | |
SYS.SCHEMAS as S | |
inner join SYS.TABLES as T on S.SCHEMA_ID = T.SCHEMA_ID | |
where exists | |
( | |
select 1 from SYS.IDENTITY_COLUMNS | |
where OBJECT_ID = T.OBJECT_ID | |
)) as B on A.TABLE_NAME = B.NAME | |
; | |
insert into @CONTROL_TABLES (TABLE_NAME, HAS_IDENTITY) | |
select | |
T.NAME, | |
1 | |
from | |
SYS.SCHEMAS as S | |
inner join SYS.TABLES as T on S.SCHEMA_ID = T.SCHEMA_ID | |
where | |
exists (select 1 from SYS.IDENTITY_COLUMNS where OBJECT_ID = T.OBJECT_ID) | |
and (T.NAME not in (select TABLE_NAME from @CONTROL_TABLES)) | |
; | |
/** | |
For each table, disable its constraints | |
**/ | |
print 'Disabling constraints…'; | |
declare NOCHECK_STATEMENTS_CURSOR cursor FAST_FORWARD for | |
select | |
distinct 'alter table ' + TABLE_NAME + ' nocheck constraint ' + CONSTRAINT_NAME + ';' | |
from | |
@CONTROL_TABLES | |
where | |
(STATUS_ENABLED = 1); | |
declare @NOCHECK_DISABLE_STATEMENT varchar(255) | |
open NOCHECK_STATEMENTS_CURSOR | |
fetch next from NOCHECK_STATEMENTS_CURSOR into @NOCHECK_DISABLE_STATEMENT | |
while (@@fetch_status <> –1) | |
begin | |
if (@@fetch_status <> –2) | |
begin | |
print @NOCHECK_DISABLE_STATEMENT | |
exec(@NOCHECK_DISABLE_STATEMENT) | |
end | |
fetch next from NOCHECK_STATEMENTS_CURSOR into @NOCHECK_DISABLE_STATEMENT | |
end | |
deallocate NOCHECK_STATEMENTS_CURSOR; | |
/** | |
For each table, disable its triggers | |
**/ | |
print 'Disabling triggers…'; | |
declare DISABLE_STATEMENTS_CURSOR cursor fast_forward for | |
select | |
'disable trigger ' + SYSOBJECTS.NAME + ' on ' + object_name(PARENT_OBJ) + ';' | |
from | |
SYSOBJECTS | |
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'); | |
declare @DISABLE_STATEMENT varchar(255) | |
open DISABLE_STATEMENTS_CURSOR | |
fetch next from DISABLE_STATEMENTS_CURSOR into @DISABLE_STATEMENT | |
while (@@fetch_status <> –1) | |
begin | |
if (@@fetch_status <> –2) | |
begin | |
print @DISABLE_STATEMENT | |
exec(@DISABLE_STATEMENT) | |
end | |
fetch next from DISABLE_STATEMENTS_CURSOR into @DISABLE_STATEMENT | |
end | |
deallocate DISABLE_STATEMENTS_CURSOR; | |
/** | |
Remove all local contents | |
**/ | |
print 'Removing local contents…'; | |
declare DELETE_STATEMENTS_CURSOR cursor fast_forward for | |
select | |
'delete from ' + TABLE_NAME + ';' | |
from | |
INFORMATION_SCHEMA.TABLES | |
where | |
(TABLE_TYPE = 'BASE TABLE') | |
order by TABLE_NAME | |
; | |
declare @DELETE_STATEMENT varchar(255) | |
open DELETE_STATEMENTS_CURSOR | |
fetch next from DELETE_STATEMENTS_CURSOR into @DELETE_STATEMENT | |
while (@@fetch_status <> –1) | |
begin | |
if (@@fetch_status <> –2) | |
begin | |
print @DELETE_STATEMENT | |
exec(@DELETE_STATEMENT) | |
end | |
fetch next from DELETE_STATEMENTS_CURSOR into @DELETE_STATEMENT | |
end | |
deallocate DELETE_STATEMENTS_CURSOR; | |
/** | |
Copy data from remote server to local environment | |
**/ | |
print 'Copying data from remote server…'; | |
—All non identity tables | |
declare COPY_STATEMENTS_CURSOR cursor fast_forward for | |
select | |
'insert into ' + TABLE_NAME + ' select * from "' + @LINKED_SERVER_NAME + '".' + @DATABASE_COPY + '.dbo.' + TABLE_NAME + ';' | |
from | |
INFORMATION_SCHEMA.TABLES | |
where | |
(TABLE_TYPE = 'BASE TABLE') | |
and (TABLE_NAME not in (select TABLE_NAME from @CONTROL_TABLES where (HAS_IDENTITY = 1))) | |
order by TABLE_NAME | |
; | |
declare @COPY_STATEMENT varchar(max); | |
open COPY_STATEMENTS_CURSOR | |
fetch next from COPY_STATEMENTS_CURSOR into @COPY_STATEMENT | |
while (@@fetch_status <> –1) | |
begin | |
if (@@fetch_status <> –2) | |
begin | |
print @COPY_STATEMENT | |
exec(@COPY_STATEMENT) | |
end | |
fetch next from COPY_STATEMENTS_CURSOR into @COPY_STATEMENT | |
end | |
deallocate COPY_STATEMENTS_CURSOR; | |
—All identity tables | |
declare @TABLE_NAME varchar(255); | |
declare @COLUMNS_NAME varchar(max); | |
declare COPY_IDENTITY_STATEMENTS_CURSOR cursor fast_forward for | |
select | |
distinct(TABLE_NAME) | |
from | |
@CONTROL_TABLES | |
where | |
(HAS_IDENTITY = 1) | |
order by TABLE_NAME | |
; | |
declare @COPY_IDENTITY_STATEMENT varchar(max); | |
open COPY_IDENTITY_STATEMENTS_CURSOR | |
fetch next from COPY_IDENTITY_STATEMENTS_CURSOR into @TABLE_NAME | |
while (@@fetch_status <> –1) | |
begin | |
if (@@fetch_status <> –2) | |
begin | |
set @COLUMNS_NAME = null; | |
select @COLUMNS_NAME = coalesce(@COLUMNS_NAME + ',', '') + COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where (TABLE_NAME = @TABLE_NAME); | |
set @COPY_IDENTITY_STATEMENT = 'set identity_insert ' + @TABLE_NAME + ' on;'; | |
set @COPY_IDENTITY_STATEMENT = @COPY_IDENTITY_STATEMENT + 'insert into ' + @TABLE_NAME + ' (' + @COLUMNS_NAME + ') select ' + @COLUMNS_NAME + ' from "' + @LINKED_SERVER_NAME + '".' + @DATABASE_COPY + '.dbo.' + @TABLE_NAME + ';' | |
set @COPY_IDENTITY_STATEMENT = @COPY_IDENTITY_STATEMENT + 'set identity_insert ' + @TABLE_NAME + ' off;'; | |
print @COPY_IDENTITY_STATEMENT; | |
exec(@COPY_IDENTITY_STATEMENT); | |
end | |
fetch next from COPY_IDENTITY_STATEMENTS_CURSOR into @TABLE_NAME | |
end | |
deallocate COPY_IDENTITY_STATEMENTS_CURSOR; | |
/** | |
For each table, enable its triggers | |
**/ | |
print 'Enabling triggers…'; | |
declare ENABLE_STATEMENTS_CURSOR cursor fast_forward for | |
select | |
'enable trigger ' + SYSOBJECTS.NAME + ' on ' + object_name(PARENT_OBJ) + ';' | |
from | |
SYSOBJECTS | |
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'); | |
declare @ENABLE_STATEMENT varchar(255) | |
open ENABLE_STATEMENTS_CURSOR | |
fetch next from ENABLE_STATEMENTS_CURSOR into @ENABLE_STATEMENT | |
while (@@fetch_status <> –1) | |
begin | |
if (@@fetch_status <> –2) | |
begin | |
print @ENABLE_STATEMENT | |
exec(@ENABLE_STATEMENT) | |
end | |
fetch next from ENABLE_STATEMENTS_CURSOR into @ENABLE_STATEMENT | |
end | |
deallocate ENABLE_STATEMENTS_CURSOR; | |
/** | |
For each table, enable its constraints | |
**/ | |
print 'Enabling constraints…'; | |
declare CHECK_STATEMENTS_CURSOR cursor fast_forward for | |
select | |
'alter table ' + TABLE_NAME + ' with check check constraint ' + CONSTRAINT_NAME + ';' | |
from | |
@CONTROL_TABLES | |
where | |
(STATUS_ENABLED = 1) | |
; | |
declare @CHECK_STATEMENT varchar(max); | |
open CHECK_STATEMENTS_CURSOR | |
fetch next from CHECK_STATEMENTS_CURSOR into @CHECK_STATEMENT | |
while (@@fetch_status <> –1) | |
begin | |
if (@@fetch_status <> –2) | |
begin | |
print @CHECK_STATEMENT; | |
exec(@CHECK_STATEMENT); | |
end | |
fetch next from CHECK_STATEMENTS_CURSOR into @CHECK_STATEMENT | |
end | |
deallocate CHECK_STATEMENTS_CURSOR; | |
print '…all good!'; |
Redirect Tomcat console messages to file
Despite the fact that offical Tomcat’s Wiki says:
System.out and System.err are both redirected to CATALINA_BASE/logs/catalina.out when using Tomcat’s startup scripts (bin/startup.sh/.bat or bin/catalina.sh/.bat). Any code that writes to System.out or System.err will end up writing to that file. (link)
For some reason that scape from my understanding, this is not 100% true. Many dirty System.out don’t go to catalina file, been readable only from console.
So, the solution to guarantee that all console messages became available in a file (usually for troubleshooting purposes) is:
- Locate the file startup.sh or startup.bat
- Look into for call “%EXECUTABLE%” start %CMD_LINE_ARGS%
- Replace the command above for call “%EXECUTABLE%” run >..\logs\console.log 2>&1 start %CMD_LINE_ARGS% run >..\logs\console.log 2>&1
Spring Framework marks transaction to rollback when everything is fine
Eventually, you can’t conclude the whole process flux because, at the end, the Spring Framework marks the transaction to rollback, even when all exceptions were caught.
The evidence of this scenario is this following message Transaction was marked for rollback only; cannot commit. The exception thrown is org.springframework.orm.jpa.JpaSystemException
.
It happens because in some point of all code processed, your flow had an exception unchecked. The simplest solution is to use checked exceptions and avoid use NoResultException
unless it is really necessary.
Generating client for SOAP using CXF
What is needed
- WSDL file describing the services
- Apache CXF (3.1+)
- JDK (1.7+)
Straight to point
In order to create the set of Client’s classes, verify for the following environment variables:
- JAVA_HOME: Path to JDK location, eg. C:\Progra~1\Java\jdk1.7.0_80
- CXF_HOME: Path to CXF location, eg. C:\Java\apache-cxf-3.1.13
Now, open a command prompt and execute the command: wsdl2java.bat -p net.itfromhell.itsatrap.serviceclient -d C:\Temp\TestClient -all -ant -exsh false -dns true -dex true -encoding UTF-8 -verbose Test.wsdl
Where…
- wsdl2java.bat: Tool to generate encapsulated service’s client from WSDL document
- net.itfromhell.itsatrap.serviceclient: Package of generated classes
- C:\Temp\TestClient: Output directory to generated classes
- Test.wsdl: WSDL file
PWC6345: There is an error in invoking javac.
Accessing your application in GlassFish you get this error message:
org.apache.jasper.JasperException: PWC6345: There is an error in invoking javac. A full JDK (not just JRE) is required
Try to verify the parameter AS_JAVA inside of file glassfish\config\asenv.bat(.conf). This parameter should be the JDK path.
Get access to DAS for other Domain than default using shell
Need to access your GlassFish as administrator but in other Domain than default. Use the tool asadmin, to do that you need to specify the port of DAS to this specific Domain:
asadmin --host localhost --port 4848 enable-secure-admin