SQL Realm

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

2012-04-11T12:52:00.000-04:00

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

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

2012-04-06T12:04:00.002-04:00

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())

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

2012-04-05T13:06:00.002-04:00

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