MS SQL Server

Search a String or Word From All the Tables in a SQL Server Database

Job History

Objective:

This procedure is to search a string or word from the entire database tables in a given database. Suppose if anyone does not know where exactly the specific string, then this will be your friend. This is tested in SQL 2000.

The technical experts should bear in mind that it may disturb the existing live performance. It is always advisable to take a copy of the database and restore it in different name and search.

CREATE PROC SearchAStringFromAllTablesInADatabase ( @SearchStr nvarchar(100) ) AS BEGIN
--This procedure created to search a string or word from all the tables from the particular database. Create this procedure in the database where you are trying to search.

--Tested on SQL Server 2000
--DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630)) or if there is an issue you can create like below temp table

--create table #results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

/*
GO
use DBNAME
go
Execute SearchAStringFromAllTablesInADatabase '172064475650'

*/

CREATE TABLE #results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
--	INSERT INTO @Results  This table variable will throw an error like execute cannot be used as a source when inserting into a table variable. Therefore, used the below temp table 
	    INSERT INTO #Results
            EXEC 
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

--SELECT ColumnName, ColumnValue FROM @Results
SELECT ColumnName, ColumnValue FROM #Results
END

 

 

9,419 thoughts on “Search a String or Word From All the Tables in a SQL Server Database”

  2. Safety and efficacy of oral etrasimod in adult
    participants with moderate-to-severe alopecia areata. U.S.

    National Library of Medicine. (2022). Study to
    evaluate safety and efficacy of jaktinib in adults with alopecia areata (AA).
    Wang, E. H. C., et al. (2018). Montagna Symposium 2017 Janus Kinase inhibitors for treatment of alopecia areata.

  4. Küçük çocuk gay seks porno ve sevimli sevimli çıplak erkek ilk kez uyku uykucu.
    Sevimli, İLk Kez, Eşcinsel, Çocuklar sevimli ücretsiz genç genç gay seks video
    xxx çıtçıt bir aldı Oral Seks, Sevimli, Emo Boys, İLk Kez, Eşcinsel, Genç Gay.
    7:00.

  6. Jürgen Klopp’un en çok tercih ettiği 4-
    yüksek saldırı taktiğidir. Taktiğin olumlu sonuçlar vermesi için güçlü takımlarda denemelisiniz.
    Liverpool takımında test edildi. Taktiği büyük boyutta görüntülemek
    için tıklayınız. Takım topa sahipken biraz daha kısa paslaşma, koşu yoluna pas yap, defanstan kısa.

  10. Gece sigarası. şükela: tümü bugün. geceleri uykusuz geçirenlerin, edebi satırlar karalayanların, hüzünlenenlerin, derin düşüncelere dalanların, dertlilerin, efkarlıların yani kısacası gece tayfasının sadık dostudur gece sigarası.
    gündüz içilen sigara ile gece içilen sigara farklıdır.

    gece içilen sigara daha başka yayılır ciğerlere, gece
    içilen.

  11. Offers more quality xxx bf video and hardcore porno than anyone.
    Watch online or download it! It’s all here, best indian porn and more porn videos.
    All you have to do is check it out and find something that strikes your fancy, you will not
    be disappointed when you see the huge amount of pron material available on this website.

  12. Very good blog! Do you have any hints for aspiring writers?
    I’m planning to start my own blog soon but I’m a
    little lost on everything. Would you recommend starting with a free platform like
    Wordpress or go for a paid option? There are so many choices out there that I’m completely confused
    .. Any suggestions? Cheers!

  15. I’m now not certain where you’re getting your info, but great topic.
    I must spend some time studying more or figuring out
    more. Thank you for magnificent info I was in search of this info for my
    mission.

  36. Learn about the side effects, dosages, and interactions. Some are medicines that help people when doctors prescribe.
    https://stromectolst.com/# ivermectin price usa
    Commonly Used Drugs Charts. Prescription Drug Information, Interactions & Side.

  128. Learn about the side effects, dosages, and interactions. Prescription Drug Information, Interactions & Side.
    cialis a domicilio new jersey
    Learn about the side effects, dosages, and interactions. Comprehensive side effect and adverse reaction information.

  135. drug information and news for professionals and consumers. What side effects can this medication cause?
    cialis vi
    Long-Term Effects. Prescription Drug Information, Interactions & Side.

  141. Medscape Drugs & Diseases. Learn about the side effects, dosages, and interactions.

    https://propeciaf.store/ cost generic propecia without prescription
    Everything what you want to know about pills. Commonly Used Drugs Charts.

  170. Cheap Glyburide online without prescription Cheap Lantus
    insulin online
    What’s the best way to obtain Metformin without a pesky prescription getting in the way?
    Cheap Glipizide with no prescription, where to order?

  248. First of all, thank you for your post. slotsite Your posts are neatly organized with the information I want, so there are plenty of resources to reference. I bookmark this site and will find your posts frequently in the future. Thanks again ^^

  253. LUBBOCK, Texas — A dust storm hit the Hub City and South Plains Sunday evening. The Mesonet at Memphis measured a 114 mph wind gust. At the airport a gust of 76 mph, mild by comparison, was recorded at 6:04 p.m. (Memphis is about a two-hour drive northeast of Lubbock.) Use the video player above NEWS10 ABC In addition to ABC’s live news coverage, ABC Live also offers premium analysis, exclusive interviews, and commentary on the latest events from around the world of politics, business, and entertainment. ABC said that during Hollywood Week, “Fan-favorite ‘American Idol’ alumni return as contestant mentors to build confidence, stage presence and songwriting” as the contestants prepare for those performances. As long as you’re connected through a VPN, you can gain instant, unlimited access to live stream all of ABC’s programming. That includes hit shows like ‘The Bachelorette’ and ‘The Bachelor,’ plus live sports like NFL, NBA, NCAA games, and more.
    http://1688-1933.com/bbs/board.php?bo_table=free&wr_id=28223&depth_1=&depth_2=
    India is sizzling at 40 degrees Celsius. Zoos have installed sprinklers and coolers for animals. Schools are shut and people are advised to stay in. IMD has issued heat wave warnings for Haryana, Uttar Pradesh, West Bengal, Bihar and Odisha. And this is just the beginning. Apple is set to open its first two India stores – but will they help its fortunes in an Android-heavy market? EnglishNigerian superstar Rema to tour India in May Opinion India is sizzling at 40 degrees Celsius. Zoos have installed sprinklers and coolers for animals. Schools are shut and people are advised to stay in. IMD has issued heat wave warnings for Haryana, Uttar Pradesh, West Bengal, Bihar and Odisha. And this is just the beginning. At least 35 dead in India temple collapse Opinion India’s top-order looks relatively settled ahead of the ICC Men’s Cricket World Cup that is scheduled to take place later this year, but they have plenty to ponder in the middle-order as Suryakumar battles it out with fellow batters Shreyas Iyer, Sanju Samson and KL Rahul for a spot in the XI.

  856. Pulsz provides a decent range of over 280 casino games. Free slots make up the majority of them. There is something for everyone, including new players and experienced players, in the portfolio of slot games. With filters to help you find what you are searching for, from three-reel classic slots to progressive jackpot slots, it’s easy to find what you want. The fun and exciting online casino games are at your finger tips. You can find all of our guides to learn how to play and win at any casino game. From strategy to tips, we have you covered. Look for your preferred game and learn all about it so you can start playing like a pro. Chumba Casino Lite performs a great job of presenting a professional and interesting experience in terms of visuals and general presentation. The games are well-designed and visually appealing. Thus, users easily get lost in the excitement of the action. Also, the app works smoothly and is easy to navigate. It is a great choice for players of all skill levels.
    https://www.tae-chang.biz/bbs/board.php?bo_table=free&wr_id=15706
    The game has a free spins bonus which is initiated through landing three or more Amazon Queen Symbols. Three symbols bring 10 free spins, four bring 25 and there are 100 free games to win with the scatter symbols. Zombie Queen is a new slot designed by Kalamba Games, offering walking multiplier wilds and bonus games, plus the chance to win up to 1,930 times your stake. Just in time for the start of the spooky season, this zombie graveyard thriller gives the player chills as they work their way to big wins. Played across 6 reels and 4 rows, this slot offers the player a whopping 50 pay lines to play on. With a haunting forest graveyard background, this slot’s scary factor is through the roof. Dark purple and pink skies frame the ghostly reels, while graveyard crosses and tombstones litter the screen.

  926. There is also toll free customer support to answer the queries of the customer. These are the features of the Destroyer Skill Stop Machine. As the name suggests, the slot machine could destroy by its appearance. These are high quality slot machines. The look and design of the machines paled in comparison to casinos in the metro and Southern Indiana. Then, bars and other businesses started to install “skill-based” machines and Mulrooney’s fundraising took a hit.  “I was pretty disappointed when the clock struck sine die last year and we didn’t kill the gray machines. I spent two months researching how to regulate gray machines after session. By the end I was just like ‘we can’t do this.’ We’re trading revenue going to charities for untaxed money going to small business owners and gaming companies,” Timoney said.
    https://gabae.co.kr/bbs/board.php?bo_table=free&wr_id=1007
    Willy Wonka Vegas Casino Slots Mod APK takes you to the world of legendary slot machines. Play is sweeter than ever with free casino slots, fun mini-games and Las Vegas-style slot machines! Phila Casino – Casino tricks in blackjack Game Status: Active You will never be bored when choosing Willy Wonka Vegas Casino Slots to entertain in your free time. I can say this because you get to meet new slot machines every two weeks. This will give you more challenges and experiences than ever before with us. Let’s join the wonderful land of Wonka to satisfy your gambling passion. You are surrounded by players with a lot of experience over the years; you must not despise your opponent. Not so for the house Donald Trump built, but video poker is fun. Will you need to move to a new home, too. You can play free games as well as real money games at these Canada casinos, the transition from land-based casinos to live online casinos and cheer each other on. I guess we need to wait for ED or HB to implement more slots, the party’s direct democracy internet platform. The famous card game, a ‘login’ window appears on the screen. I dont 100% think BW is a good investment either, login and go to the player management section to create your players.