Tag: "just code"

Zahlen eines Integers aufaddieren

Posted on Okt 17, 2010 von in SQL Server
DECLARE @i int, @l int;
SELECT
@i = 12, @l = LEN(@i);

SELECT
SUM(x.Digit)
FROM
(SELECT
CAST(SUBSTRING(RTRIM(@i), N.Number, 1) AS int) AS Digit
FROM
dbo.Number N
WHERE
N.Number BETWEEN 1 AND LEN(RTRIM(@i))
) x;

Tabellen ohne Indizes

Posted on Aug 24, 2010 von in SQL Server

Hier sind diverse Möglichkeiten um zu überprüfen, für welche Tabellen kein Clustered Index definiert wurde:

Ganze Geschichte »

Alle SQL-Server im Netzwerk anzeigen

Posted on Jan 12, 2009 von in SQL Server
USE AdventureWorks;
GO
EXEC xp_cmdshell 'SQLCMD -L c';
GO

Fremdschlüssel Aktion Check

Posted on Dez 29, 2008 von in SQL Server
SELECT
    OBJECTPROPERTY(O.[OBJECT_ID], 'CnstIsUpdateCascade') AS updcascade,
    OBJECTPROPERTY(O.[OBJECT_ID], 'CnstIsDeleteCascade') AS delcascade,
    [name]
FROM
    sys.objects O
WHERE
    OBJECTPROPERTY(O.[OBJECT_ID], 'IsForeignKey') = 1 AND
    (OBJECTPROPERTY(O.[OBJECT_ID], 'CnstIsUpdateCascade') = 1 OR OBJECTPROPERTY(O.[OBJECT_ID], 'CnstIsDeleteCascade') = 1)
ORDER BY 1 DESC, 3

Durchschnittliche Zeilenlänge anzeigen

Posted on Dez 29, 2008 von in SQL Server

 

DECLARE @TableID int
SELECT @TableID = OBJECT_ID ('Enter TableName here')

SELECT 
	OBJECT_NAME (IXStats.[object_id]) AS ObjectName,
    IXStats.index_type_desc,
    IXStats.avg_record_size_in_bytes
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), @TableID, OBJECTPROPERTY (@TableID, 'TableHasClustIndex'), 0, 'SAMPLED') IXStats

 

Tags: ,

Feststellen, ob eine Tabelle einen Trigger besitzt

Posted on Dez 11, 2008 von in SQL Server
SELECT 
    CASE 
        WHEN OBJECTPROPERTY(OBJECT_ID('dbo.BookSource'), 'HasAfterTrigger') + OBJECTPROPERTY(OBJECT_ID('dbo.BookSource'), 'HasInsteadOfTrigger') > 0 
        THEN 'Trigger vorhanden' 
        ELSE 'Kein Trigger vorhanden' 
END 
GO 
IF EXISTS (SELECT 1 
        FROM 
            sys.triggers T 
        WHERE 
            T.parent_id = OBJECT_ID ('dbo.BookSource'))
    SELECT 'Trigger vorhanden' 
ELSE 
    SELECT 'Kein Trigger vorhanden'

Informationen zu Trigger

Posted on Dez 4, 2008 von in SQL Server
SELECT
    T.[name] AS Trigger_Name,
    OBJECT_NAME(T.parent_id) AS Table_Name,
    TE.type_desc,
    T.create_date
FROM
    sys.triggers T
    JOIN
    sys.trigger_events TE ON T.[object_id] = TE.[object_id];

Objektberechtigungen anzeigen

Posted on Okt 21, 2008 von in SQL Server
DECLARE @string sysname;

SELECT @string = '< search string >';

SELECT
OBJECT_NAME(major_id) AS ObjectName,
USER_NAME(grantor_principal_id) AS Grantor,
USER_NAME(grantee_principal_id) AS Grantee,
p.permission_name
FROM
sys.database_permissions p
WHERE
OBJECTPROPERTY(major_id, 'IsMSSHipped') = 0 AND
USER_NAME(grantee_principal_id) = @string
ORDER BY
OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name;