Karuta’s ASP & M$ SQLserver

Dicas, códigos e outras coisinhas de meus estudos para não esquecer…

Archive for the ‘sql server’ Category

Descobrir registros duplicados em uma tabela SQL

Posted by karuta em outubro 13, 2012

SELECT * FROM tabela t1 WHERE EXISTS ( SELECT * FROM tabela t2 WHERE t2.campo = t1.campo GROUP BY campo HAVING COUNT(*) > 1 ) ORDER BY lista de campos

ou

SELECT * FROM tabela WHERE campo IN ( SELECT campo FROM tabela GROUP BY campo HAVING count(*) > 1) ORDER BY lista de campos

Posted in sql server, store procedure | Leave a Comment »

Posted by karuta em outubro 11, 2012

Quantas tabelas criadas pelo usuário existem no banco de dados?
Qual o número de índice de cada tabela?
Heap Tables*  ou clustered index ?
Quantas linhas tem cada tabela ?

* tabelas que não possuem índice cluster

SELECT [schema_name] = s.name, table_name = o.name,
MAX(i1.type_desc) ClusteredIndexorHeap,
COUNT(i.TYPE) NoOfNonClusteredIndex, p.rows
FROM sys.indexes i
RIGHT JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
LEFT JOIN sys.partitions p ON p.OBJECT_ID = o.OBJECT_ID AND p.index_id IN (0,1)
LEFT JOIN sys.indexes i1 ON i.OBJECT_ID = i1.OBJECT_ID AND i1.TYPE IN (0,1)
WHERE o.TYPE IN ('U')
GROUP BY s.name, o.name, p.rows
ORDER BY schema_name, table_name

Posted in sql server | Leave a Comment »

SQL Compact data and schema script utility

Posted by karuta em outubro 7, 2012

Ferramenta que é uma mão na roda. Integra Microsoft SQL Server Management Studio e é capaz de gerar um schema/script de uma base SQL Server Express 2008 convertido para SQL Compact. Uma conversão simples e rápida.

Detalhes em http://exportsqlce.codeplex.com/

Posted in sql server | Leave a Comment »

SQL SERVER – Upper Case Shortcut SQL Server Management Studio

Posted by karuta em setembro 29, 2012

Few days ago, I received code which is very similar to code shown below.
select *
from Sales.SalesOrderDetail
where ProductID > 777

I am not the guy who go crazy for formatting but I do appreciate proper coding. I like if the code was formatted like below.

SELECT *
FROM Sales.SalesOrderDetail
WHERE ProductID > 777

The fastest way one can do this in SSMS is either search and replace or using SSMS short cut to covert keywords to upper case.

What I do is I select the word and hit CTRL+SHIFT+U and it SSMS immediately changes the case of the selected word. Similar way if one want to convert cases to lower case, another short cut CTRL+SHIFT+L is also available.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in sql server, store procedure | Leave a Comment »

SQL Server 2005/2008 VS MySQL 5.0 – Comparison of SQL Server and MySQL database features

Posted by karuta em junho 29, 2012

Comparison of SQL Server and MySQL database features.

Feature SQL Server 2005/2008 MySQL 5.1
1. OS Support Windows only Support all – Windows, Linux, Unix, Mac
2. Licensing Commercial Open Source, Free
3. Graphical View Designer Yes No
4. GUI Tool Yes Yes
5. Drivers Installed (windows), ODBC, JDBC, NET, PHP drivers available No (windows), ODBC, JDBC, NET, PHP drivers available
6. Views Indexed views Updateable views
7. Read-Only Views Yes Yes
8. Updateable Views Yes – even for 2 table views will automatically Yes – Single one table views are automatically
9. Computed Columns – COUNT(DISTINCT), AGGREGATE(DISTINCT) Yes No
10. Foreign Key – Cascade Update/Delete Yes InnoDB and not MyISAM
11. Set returning values used in FROM clause Yes No
12. Triggers Yes Yes
13. Functions Yes Yes
14. Stored Procedure Yes Yes
15. Multi Row value insert No(2005), Yes(2008) Yes
16. ACID Transactions Yes Yes
17. Table Partitioning Yes Yes
18. Access tables from other databases on same server Yes Yes
19. Case-Insensitivity Yes No
20. Authentication Yes Yes
21. ROLLUP/CUBE Yes Yes
22. Schemas Yes Yes
23. CROSS APPLY Yes No
24. LIMIT .. OFFSET No, not directly Yes
25. Advanced Database Tuning Wizard Yes No
26. Maintenance Plan Wizard Yes No
27. Pluggable Storage Engine No Yes
28. FullText Engine Yes Yes
29. Sequences /Auto Number Yes – IDENTITY Yes – AUTO_INCREMENT
30. Referential Integrity Yes Yes
31. Temporary Table Yes Yes
32. Cursor Yes Yes
33. External routine Yes Yes
34. XML support Yes No, (MySQL mentioned supporting XML and XPATH, I don’t know)
35. Unicode Yes Yes
36. Backups Yes Yes
37. Replication Snapshot, Transactional, Merge One-way only
38. Clustering Yes Yes
39. Security features – Grant, revoke Yes Yes
40. Locking and concurrency support Fully automated Row-locking (InnoDB)
42. Correlated SubQueries, Joins, Prepared Statements Yes Yes
43. Transactions – Commit, Rollback Yes Yes
44. Indexing Yes Yes(clustered, b-tree, hash, full-text)
45. Data encryption and decryption Yes Yes

Posted in sql server | Leave a Comment »

Gerar JSON em qualquer cláusula SQL – Consuming JSON Strings in SQL Server

Posted by karuta em agosto 31, 2011

CREATE PROCEDURE [dbo].[GetJSON] (
@ParameterSQL AS VARCHAR(MAX)
)
AS
BEGIN

DECLARE @SQL NVARCHAR(MAX)
DECLARE @XMLString VARCHAR(MAX)
DECLARE @XML XML
DECLARE @Paramlist NVARCHAR(1000)
SET @Paramlist = N'@XML XML OUTPUT'
SET @SQL = 'WITH PrepareTable (XMLString) '
SET @SQL = @SQL + 'AS ( '
SET @SQL = @SQL + @ParameterSQL+ ' FOR XML RAW, TYPE, ELEMENTS '
SET @SQL = @SQL + ') '
SET @SQL = @SQL + 'SELECT @XML = XMLString FROM PrepareTable '
EXEC sp_executesql @SQL, @Paramlist, @XML=@XML OUTPUT
SET @XMLString = CAST(@XML AS VARCHAR(MAX))

DECLARE @JSON VARCHAR(MAX)
DECLARE @Row VARCHAR(MAX)
DECLARE @RowStart INT
DECLARE @RowEnd INT
DECLARE @FieldStart INT
DECLARE @FieldEnd INT
DECLARE @KEY VARCHAR(MAX)
DECLARE @Value VARCHAR(MAX)

DECLARE @StartRoot VARCHAR(100); SET @StartRoot = '<row>'
DECLARE @EndRoot VARCHAR(100); SET @EndRoot = '</row>'
DECLARE @StartField VARCHAR(100); SET @StartField = '<'
DECLARE @EndField VARCHAR(100); SET @EndField = '>'

SET @RowStart = CharIndex(@StartRoot, @XMLString, 0)
SET @JSON = ''
WHILE @RowStart > 0
BEGIN
	SET @RowStart = @RowStart+Len(@StartRoot)
	SET @RowEnd = CharIndex(@EndRoot, @XMLString, @RowStart)
	SET @Row = SubString(@XMLString, @RowStart, @RowEnd-@RowStart)
	SET @JSON = @JSON+'{'

	-- for each row
	SET @FieldStart = CharIndex(@StartField, @Row, 0)
	WHILE @FieldStart > 0
	BEGIN
		-- parse node key
		SET @FieldStart = @FieldStart+Len(@StartField)
		SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
		SET @KEY = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)
		SET @JSON = @JSON+'"'+@KEY+'":'

		-- parse node value
		SET @FieldStart = @FieldEnd+1
		SET @FieldEnd = CharIndex('</', @Row, @FieldStart)
		SET @Value = SubString(@Row, @FieldStart, @FieldEnd-@FieldStart)
		SET @JSON = @JSON+'"'+@Value+'",'

		SET @FieldStart = @FieldStart+Len(@StartField)
		SET @FieldEnd = CharIndex(@EndField, @Row, @FieldStart)
		SET @FieldStart = CharIndex(@StartField, @Row, @FieldEnd)
	END	
	IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))
	SET @JSON = @JSON+'},'
	--/ for each row

	SET @RowStart = CharIndex(@StartRoot, @XMLString, @RowEnd)
END
IF LEN(@JSON)>0 SET @JSON = SubString(@JSON, 0, LEN(@JSON))
SET @JSON = '[' + @JSON + ']'
SELECT @JSON

END

exemplo de uso:
EXEC GetJSON 'SELECT * FROM dbo.Employee_TBL'
retorno do select:
[{"UserId":"7C92EB27-DD81-498E-82CE-18192C940328","FirstName":"Bill","LastName":"Gates","Age":"45","LastLogin":"2008-11-02T00:00:00"},{"UserId":"58E75687-8D0F-423A-8A76-4B1D750F62FD","FirstName":"Steve","LastName":"Job","Age":"52","LastLogin":"2008-10-17T00:00:00"}]

Posted in sql server, store procedure | Leave a Comment »

LISTAR TODAS Stored Procedures,Tables e Views

Posted by karuta em maio 20, 2011

Hoje me aconteceu uma situação nova, preciso listar todas tabelas e sp’s na impressora… a solução mais rápida foi essa:

1.  Stored Procedures
Select name From sys.objects Where type = ‘P’

2.  Tables
Select name From sys.objects Where type = ‘U’

3.Views
Select name From sys.objects Where type = ‘V’

Posted in sql server, store procedure | Leave a Comment »

Mostrar o número de dias de um determinado mês

Posted by karuta em abril 29, 2011

CREATE FUNCTION [dbo].[GetDaysInMonth] ( @day DATETIME )
RETURNS INT
AS
BEGIN

RETURN CASE WHEN MONTH(@day) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
 WHEN MONTH(@day) IN (4, 6, 9, 11) THEN 30
 ELSE CASE WHEN (YEAR(@day) % 4 = 0 AND --Leap Year
 YEAR(@day) % 100 != 0) OR
 (YEAR(@day) % 400 = 0)
 THEN 29
 ELSE 28
 END
 END

END
GO

Posted in sql server | Leave a Comment »

Remover = tab – line feed – carriage return

Posted by karuta em abril 29, 2011

REPLACE(REPLACE(REPLACE([TextField], CHAR(9), ”), CHAR(10), ”), CHAR(13), ”)

Posted in sql server, store procedure | Leave a Comment »

Reiniciando o valor da chave primária

Posted by karuta em fevereiro 6, 2011

No SQL Server podemos reiniciar o valor da chave primária de uma tabela com o simples comando abaixo.

DBCC CHECKIDENT (‘Produtos’, RESEED, 0)

‘Produtos’ é o nome da Tabela e o número 0 é o valor que deseja iniciar sua chave primária.

Posted in sql server | 1 Comment »

 
Seguir

Obtenha todo post novo entregue na sua caixa de entrada.