SQL Server'da text split etme

Unutmamak için buraya ekledim. İnceledikten sonra konuyla ilgili buraya bir şeyler 
karalayacağım.

SELECT Source         = q.value('(/n[1])', 'varchar(10)'),
       RecordType     = q.value('(/n[2])', 'varchar(20)'),
       RecordNumber   = q.value('(/n[3])', 'int'),
       Status         = q.value('(/n[4])', 'varchar(5)')
FROM   (
         SELECT   q = Convert(xml,'<n>'+Replace(fieldName,'.','</n><n>')+'</n>')
         FROM     some_TABLE
       ) Q

'How do I split a string so I can access item x?

SQL Server'da bir tabloya ait kolon bilgilerinin alınması. 3 farklı seçenek.

SQL Server'da bir tabloya ait kolon bilgilerinin alınması. 3 farklı seçenek.

Seçenek 1:

SELECT ISNULL(COL.COLUMN_NAME,'') as Name

    ,ISNULL(COL.DATA_TYPE, '') as Type

    ,ISNULL(COL.NUMERIC_SCALE,0)  as Numeric_Scale  

    ,ISNULL(COL.NUMERIC_PRECISION, 0) as Precision  

    ,ISNULL(COL.CHARACTER_MAXIMUM_LENGTH, 0) as LenStr 

    ,CASE WHEN ISNULL(KEYCOLUSE.COLUMN_NAME,'0') = '0' THEN 0 ELSE 1 END  as isPrimaryKey

FROM INFORMATION_SCHEMA.COLUMNS as COL

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as KEYCOLUSE

ON KEYCOLUSE.TABLE_NAME = COL.TABLE_NAME

AND KEYCOLUSE.COLUMN_NAME = COL.COLUMN_NAME

WHERE COL.TABLE_NAME = 'TableName'

    AND COL.TABLE_SCHEMA = 'dbo'

ORDER BY COL.ORDINAL_POSITION

Seçenek 2:

SELECT C.name AS Name,

       T.name AS Type,

       ISNULL(C.scale, 0) AS Numeric_Scale,

       ISNULL(C.precision, 0) AS Precision,

       (CASE WHEN T.name LIKE '%char%' THEN ISNULL(C.max_length, 0) ELSE 0 END) AS LenStr,

       (CASE WHEN ISNULL(P.column_id,0)>0 THEN 1 ELSE 0 END) As isPrimaryKey

FROM sys.columns AS C

LEFT JOIN (

             SELECT  ic.object_id, ic.column_id

             FROM    sys.indexes AS i INNER JOIN

             sys.index_columns AS ic ON  i.object_id = ic.object_id AND i.index_id = ic.index_id

             WHERE   i.is_primary_key = 1

) AS P ON C.object_id = P.object_id AND C.column_id = P.column_id

INNER JOIN sys.types T on T.system_type_id = C.system_type_id

WHERE OBJECT_NAME(C.object_id) = 'TableName'

ORDER BY C.column_id 

Seçenek 3:

SELECT C.id,C.name  AS Name,

             T.name AS Type,

             ISNULL(C.scale, 0) AS Numeric_Scale,

             ISNULL(C.prec, 0) AS Precision,

             (CASE WHEN LOWER(T.name) LIKE '%char%' THEN ISNULL(C.length, 0) ELSE 0 END) AS LenStr,

             (CASE WHEN ISNULL(P.colid,0)>0 THEN 1 ELSE 0 END) As isPrimaryKey

FROM syscolumns AS C

LEFT JOIN (

                    SELECT  ic.id, ic.colid

                    FROM    sysindexkeys AS ic

) AS P ON C.id = P.id AND C.colid = P.colid

INNER JOIN systypes T on T.xtype = C.xtype AND T.status=0

WHERE OBJECT_NAME(C.id) = 'TableName'

ORDER BY C.colorder 

* How to find out column names of a table in SQL Server

"Cannot resolve collation conflict for DISTINCT operation." hatası

Database sunucusu ile veritabanınız farklı collationlara sahip ve "Cannot resolve collation conflict for DISTINCT operation." hatası alıyorsunuz. Öyleyse yapmanız gereken aşağıdaki gibi kodunuza  COLLATE "CollationAdi" eklemek.

SELECT DISTINCT (ISNULL(D.DemirbasCinsi,'') + '-' + ISNULL(V.ParaKodu,'')) COLLATE SQL_Latin1_General_CP1_CI_AS AS KolonAdi
FROM v_Demirbas AS D
INNER JOIN tbl_VUK AS V ON D.dmb_barkod  COLLATE SQL_Latin1_General_CP1_CI_AS =V.dmb_barkod  COLLATE SQL_Latin1_General_CP1_CI_AS

SQL Server'da log dosyasını shrink etmek (kesin çözüm)

SQL Server'da veritabanı ve log dosyasını shrink etmek (Kesin Çözüm)

ALTER DATABASE VeritabaniAdi SET RECOVERY SIMPLE 
GO
DBCC SHRINKDATABASE  ( 'VeritabaniAdi', 0 )
GO
ALTER DATABASE VeritabaniAdi  SET RECOVERY FULL 

VB.NET ile SQL Server'a Bağlanmak.

VB.NET ile SQL Server'a bağlanmak ve sorgu sonucunu datatable'a atmak.

        'Imports System.Data.SqlClient

        Dim con As New SqlClient.SqlConnection
        Dim strCon As String = "Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;Connection Timeout=10;" 'NT Authentication
        'For SQL Authentication Dim strCon As String = "Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User ID=USERNAME;Password=PASSWORD;Connection Timeout=50;"
        Dim strCommand As String = "SELECT * FROM TABLE"
        Dim command As SqlCommand
        Dim da As SqlDataAdapter
        Dim dt As New DataTable
        Try
            con.ConnectionString = strCon
            command = New SqlCommand(strCommand, con)
            command.CommandTimeout = 3000

            da = New SqlDataAdapter(command)
            da.Fill(dt)
            MsgBox(dt.Rows.Count.ToString())



            'FOR INSERT, UPDATE, DELETE, USE BELOW

            'Dim count as Integer = command.ExecuteNonQuery()

            'count is the affected row number

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error")
        Finally
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try

SQL SERVER'da bir kolonun adını değiştirmek.

Herhangi bir kolonun adını değiştirmek için,

sp_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN'

Herhangi bir nesnenin adını değiştirmek için (table, sp vs.) :
sp_RENAME '[OldTableName]' , '[NewTableName]'