Wednesday, April 11, 2012

SQL 2000-12 Find any Table, Column, Data Type by Name

FindTableColumnByName.sql
-- Parameter Declaration
DECLARE @TableName VARCHAR(250)
    , @ColumnName VARCHAR(250)
    , @DataType VARCHAR(250)

-- Table to be found, Any Column, Any DataType
SELECT
    @TableName = 'AddressType'
    , @ColumnName = '%'
    , @DataType = NULL

SELECT
    ISC.TABLE_SCHEMA AS TableSchema
    , ISC.TABLE_NAME AS TableName
    , ISC.COLUMN_NAME AS ColumnName
    , UPPER(ISC.DATA_TYPE) +
        COALESCE('('+CAST(ISC.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')','')
    AS DataType
    , ISNULL(ISC.COLLATION_NAME,'') AS ColumnCollation
    , ISNULL(CASE
        WHEN SC.id IS NOT NULL THEN 'IDENTITY(' +
            CAST(IDENT_SEED(ISC.TABLE_SCHEMA + '.' + ISC.TABLE_NAME) AS VARCHAR) + ',' +
            CAST(IDENT_INCR(ISC.TABLE_SCHEMA + '.' + ISC.TABLE_NAME) AS VARCHAR) + ')'
    END,'') AS [Identity]
    , CASE
        WHEN ISC.IS_NULLABLE = 'No' THEN 'NOT '
        ELSE ''
    END + 'NULL' AS Nullable
    , ISNULL(ISC.COLUMN_DEFAULT,'') AS ColumnDefault
    , ISNULL(ISCCU.CONSTRAINT_NAME,'') AS ColumnConstraint
FROM INFORMATION_SCHEMA.COLUMNS ISC
    LEFT OUTER JOIN syscolumns SC
        ON OBJECT_NAME(SC.id) = ISC.TABLE_NAME
            AND SC.name = ISC.COLUMN_NAME
            AND COLUMNPROPERTY(SC.id, SC.name, 'IsIdentity') = 1
    LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ISCCU
        ON ISCCU.TABLE_NAME = ISC.TABLE_NAME
            AND ISCCU.COLUMN_NAME = ISC.COLUMN_NAME
WHERE 1 = 1
    AND ISC.TABLE_NAME LIKE ISNULL(@TableName,'%')
    AND ISC.COLUMN_NAME LIKE ISNULL(@ColumnName,'%')
    AND ISC.DATA_TYPE LIKE ISNULL(@DataType,'%')
ORDER BY ISC.TABLE_NAME, ISC.COLUMN_NAME

Friday, April 6, 2012

SQL 2000 2005 2008 2012 Find any Table, Column, Data Type by Name

SQL 2000 2005 2008 2012 Find any Table, Column, & Data Type by Name

-- Parameter Declaration
DECLARE @TableName VARCHAR(250)
    , @ColumnName VARCHAR(250)
    , @DataType VARCHAR(250)

-- Table to be found, Any Column, Any DataType
SELECT
    @TableName = 'AddressType'
    , @ColumnName = '%'
    , @DataType = NULL

SELECT
    ISC.TABLE_SCHEMA AS TableSchema
    , ISC.TABLE_NAME AS TableName
    , ISC.COLUMN_NAME AS ColumnName
    , UPPER(ISC.DATA_TYPE) +
        COALESCE('('+CAST(ISC.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')','')
    AS DataType
    , ISNULL(ISC.COLLATION_NAME,'') AS ColumnCollation
    , ISNULL(CASE
        WHEN SC.id IS NOT NULL THEN 'IDENTITY(' +
            CAST(IDENT_SEED(ISC.TABLE_SCHEMA + '.' + ISC.TABLE_NAME) AS VARCHAR) + ',' +
            CAST(IDENT_INCR(ISC.TABLE_SCHEMA + '.' + ISC.TABLE_NAME) AS VARCHAR) + ')'
    END,'') AS [Identity]
    , CASE
        WHEN ISC.IS_NULLABLE = 'No' THEN 'NOT '
        ELSE ''
    END + 'NULL' AS Nullable
    , ISNULL(ISC.COLUMN_DEFAULT,'') AS ColumnDefault
    , ISNULL(ISCCU.CONSTRAINT_NAME,'') AS ColumnConstraint   
FROM INFORMATION_SCHEMA.COLUMNS ISC
    LEFT OUTER JOIN syscolumns SC
        ON OBJECT_NAME(SC.id) = ISC.TABLE_NAME
            AND SC.name = ISC.COLUMN_NAME
            AND COLUMNPROPERTY(SC.id, SC.name, 'IsIdentity') = 1
    LEFT OUTER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ISCCU
        ON ISCCU.TABLE_NAME = ISC.TABLE_NAME
            AND ISCCU.COLUMN_NAME = ISC.COLUMN_NAME
WHERE 1 = 1
    AND ISC.TABLE_NAME LIKE ISNULL(@TableName,'%')
    AND ISC.COLUMN_NAME LIKE ISNULL(@ColumnName,'%')
    AND ISC.DATA_TYPE LIKE ISNULL(@DataType,'%')
ORDER BY ISC.TABLE_NAME, ISC.COLUMN_NAME

TableSchema
TableName
ColumnName
DataType
ColumnCollation
Person
AddressType
AddressTypeID
INT
Person
AddressType
ModifiedDate
DATETIME
Person
AddressType
Name
NVARCHAR(50)
Latin1_General_CS_AS
Person
AddressType
rowguid
UNIQUEIDENTIFIER
Continued

Identity
Nullable
ColumnDefault
ColumnConstraint
IDENTITY(1,1)
NULL
PK_AddressType_AddressTypeID
NULL
(getdate())
NULL
NULL
(newid())

Thursday, April 5, 2012

SQL 2000 2005 2008 2012 Find Index of Character by Occurrence in String

Description

Find Index of Character by Occurrence in String.  Sorry for not leaving an elaborate description but I feel the code is pretty straight forward.  Please feel free to ask any questions or leave any comments.

Script

-- Parameter Declaration
DECLARE @CharacterString VARCHAR(250)
    , @SearchString VARCHAR(250)
    , @Counter INT

-- Optional Number Table, See commented FROM below
DECLARE @tblNumber TABLE ( Number INT )

-- String to be searched, String to find, Set @Counter
SELECT @CharacterString = 'Life can only be understood backwards, but it ' +
    'must be lived forward.'
    , @SearchString = ' '
    , @Counter = 1

-- INSERT an Incremental Number for each character in Search String
WHILE @Counter < LEN(@CharacterString)
BEGIN
    INSERT INTO @tblNumber VALUES ( @Counter )
    SET @Counter = @Counter + 1
END

SELECT
    SearchStringOccurrence
    , CharacterIndex
    , SUBSTRING(@CharacterString,1,CharacterIndex) AS StartToFoundIndex
FROM
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY N.Number) AS SearchStringOccurrence
        , N.Number AS CharacterIndex
    FROM @tblNumber N
    --FROM (
    --    SELECT (N.number + 1) AS Number
    --    FROM [master].dbo.spt_values N
    --    WHERE N.[type] = 'P'
    --) N
    WHERE 1 = 1
        AND N.Number <= LEN(@CharacterString)
        AND SUBSTRING(@CharacterString, N.Number, DATALENGTH(@SearchString)) = @SearchString
) O
--WHERE O.SearchStringOccurrence = 4 -- "Life can only be "

Results

SearchStringOccurrence
CharacterIndex
StartToFoundIndex
1
5
Life
2
9
Life can
3
14
Life can only
4
17
Life can only be
5
28
Life can only be understood
6
39
Life can only be understood backwards,
7
43
Life can only be understood backwards, but
8
46
Life can only be understood backwards, but it
9
51
Life can only be understood backwards, but it must
10
54
Life can only be understood backwards, but it must be
11
60
Life can only be understood backwards, but it must be lived